How to Restrict Creating, Altering, Deleting Tables from a Database on specific date or on specific day using Trigger

What is a trigger

A trigger is a special kind of stored procedure which will execute on DDL or DML queries.

• Trigger is a programme used to perform specific task when it is executed.
• To overcome the drawbacks of constraints Trigger are used.
• Constraints are works only on table labels but trigger are works on Table label,Database Label and on whole Server Label.
• The second purpose of trigger is to perform Custom validation on database or on table we need trigger(or we can say it as DataIntegrity).
• Triggers are of 2 types.

• Again DML TRIGGERS are of two types.

-Instead Of Trigger

-After Trigger

Q) What is instead of trigger?

 

 

1. Instead of saving/Updating/Deleting in main table the data will be saved /updated/deleted first in magic table here.
2. From the magic table we can give our custom validation
3. After inserting in magic table if we have putted and condition or validation it will check ,if the data is valid then it will save to the main table otherwise it will not save/update/delete.

Q) what is after trigger?

As the name suggest this trigger will fire after any DML operation

1. Here data will be saved in main table first then the trigger will execute and data will be insert into magic table.

 

Now we will come to the main point how to restrict Creating,altering,Deleting tables from a database on specific date or on specific day using Trigger

Restricting CREATING,Alter,Drop tables from a database on any date

create trigger tr8 on database for create_table,Alter_table,Drop_table
as
begin
declare @date date
select @date=GETDATE()
if @date='5/12/2015'
begin
print 'No data will be inserted today'
rollback
end
else
begin
print 'table created'
end
end

Here i have taken today date and checking if the date is '5/12/2015' i am not allowing any operation on my database

 

To drop this trigger

drop trigger tr8 on database

when dropping the trigger on DB you must choose the master database.

 

Create a trigger which will not allow the user to create ,alter,drop any table from database on SUNDAY

create trigger trx on database for create_table,Alter_table,Drop_table
as
begin
declare @today nvarchar(10)
select @today=DATENAME(weekday,getdate())
if @today='Sunday'
begin
print 'No data will be inserted today'
rollback
end
end

If we try to create a table on sunday it will give error.

 

Thus in this way we can give restriction on table label,database label,and on server label to restrict any operation using trigger.