I was asked following questions when discussing security issues in meeting with offshore team of large database consultancy company few days ago. I will only discuss one of the security issue was discussed accidental data modification by developers and DBAs themselves. Let’s learn about Server and Database Level DDL Triggers Examples and Explanation here.
How to alter modification in database by system admin himself?
How to prevent accidents due to fat fingers? (Accidental execution of code)
How to display message to contact another developer when another developer tries to modify object another developer working on?
It was an interesting conversation. The answer to all the questions is the correct assignment of permissions and (when permissions are not an issue) Server and Database Level DDL Triggers. If developers have their own login to SQL Server and it does not have permissions to drop or create objects this will not be issued at all. However, there is still a risk of System Admin himself making accidental mistakes. The solution to this will be use Server and Database Level DDL Triggers.
DDL is abbreviation of Data Definition Level. DDL contains the schema of the database object. It was always dreaming of all DBA, when a change in the mission critical schema of the database or server is attempted it is prevented immediately informing DBA and users automatically. DDL Trigger can now make this dream true. Definition of DDL Trigger (from BOL) is DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.
DML is abbreviation of Data Manipulation Level. DML contains the actual data which is stored in the database schema. UPDATE, INSERT, DELETE, SELECT is clause are used to manipulate database. There is following a different between DML and DDL triggers.
- DDL triggers do not support INSTEAD OF feature DML triggers.
- DDL triggers do not have a feature of inserted and deleted tables like DML triggers as it does not participate in database manipulations.
Following example demonstrates how DDL trigger can be used to prevent dropping stored procedure.
DDL Trigger
Step 1 :
First, create any sample stored procedure.
USE AdventureWorks; GO CREATE PROCEDURE TestSP AS SELECT 1 test; GO
Step 2 :
Create DDL trigger which will prevent dropping the stored procedure.
USE AdventureWorks GO CREATE TRIGGER PreventDropSP ON DATABASE FOR DROP_PROCEDURE AS PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script. Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP> ENABLE TRIGGER PreventDropSP ON DATABASE;' ROLLBACK; GO
Step 3 :
Now test above trigger by attempting to drop the stored procedure.
USE AdventureWorks GO DROP PROCEDURE TestSP; GO
This should throw following message along with error code 3609 :
Dropping Procedure is not allowed.
DDL Trigger is preventing this from happening.
To drop stored procedure run following script.
Script:
DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Step 4 :
Now DISABLE above trigger and it will let you successfully drop the stored procedure previously attempted to drop. Once it is dropped enable trigger again to prevent future accidents.
USE AdventureWorks GO DISABLE TRIGGER PreventDropSP ON DATABASE; DROP PROCEDURE TestSP; ENABLE TRIGGER PreventDropSP ON DATABASE; GO
List of all the DDL events (DROP_PROCEDURE in the example above) to use with DDL Trigger are listed on MSDN.
If you want to download local copycopy, click here.
Reference: Pinal Dave (http://www.SQLAuthority.com), BOL
50 Comments. Leave new
hello sir,
can we pass parameter to trigger
No. It is not possible
Thank you sir.
Dear Sir i am using below code in Sql2005 and getting error . Please help me.
USE Test
GO
CREATE TRIGGER PreventDropSP
ON Test
FOR DROP_PROCEDURE
AS
PRINT ‘Dropping Procedure is not allowed.’
ROLLBACK;
GO
“””Msg 170, Level 15, State 1, Procedure PreventDropSP, Line 6
Line 6: Incorrect syntax near ‘DROP_PROCEDURE’.””””””
hai ved,
use test
go
create trigger preventdropsp
on database
for drop_procedure
as
print PRINT ‘Dropping Procedure is not allowed.’
ROLLBACK;
GO
Regards
kalyan
Hello Ved,
Write this as following:
CREATE TRIGGER PreventDropSP
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT ‘Dropping Procedure is not allowed.’
ROLLBACK;
GO
Regards,
Pinal Dave
Dear Sir
Thanks for your reply .However i am getting same error ..
Can you please give me any one of DDL Trigger Example.
Thanks
Ved
Hello,
I wish to track when anyone does INSERT,UPDATE, DELETE on any of the tables in a database. Is it possible to write database level trigger for this case?
Thanks,
-Saroj
hi sir i m creating trigger as
create trigger check_id
ON dbo.teacher
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF(t_no<100)
THEN
print('not allowed');
END;
but getting an error
Msg 156, Level 15, State 1, Procedure check_id, Line 8
Incorrect syntax near the keyword 'THEN'
pls. help me as ma a beginner in sql .
Hi,
There is an syntax error in the IF. check this out.
create trigger check_id
ON dbo.interest_rate
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF(select Interest_rate from inserted) <100
print('not allowed')
END
Help Plz..
Well hello there.. i am facing problem in updating using trigger
following the trigger..
well my requirements is to update the column . I want to store(update) the prev. value while updating in another columns correspond to id.
like prevsal and newsalary.
following trigger which i have created…
*as we know when we update then (inserted and deleted will fire also in which the old value which we can retrieve using (select * from deleted))
ALTER trigger [dbo].[tb] on [dbo].[tb_name]
for insert, update
as
declare @count int
declare @loop int
declare @empid int
select @loop =1
select @count = (select * from tblname)
WHILE(@loop <= @count)
update tblname set newsalary=1200 where id=@loop and date='12/12/2010'
set @loop= @loop+1
update tblname set prevsal=(select newsalary from deleted)
End
*if there any Error then let me know well i am updating the field with column name newsalary but for column name prevsal they givng me NULL value except old value..
hope you can understand my requirement
with regards
vikram
Hi Phillip,
I am trying to create a simple DDL trigger on server level, everything is fine on my local machine but when I am trying on our server (SQL 2005 Clustered) I am getting Error, any idea?
Create TRIGGER test
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
print ‘Hello’
END
Msg 1098, Level 15, State 1, Procedure test, Line 7
The specified event type(s) is/are not valid on the specified target object.
i want to execute the trigger whenever the new login is created i.e. new entry added to master..syslogins table ,
Which server trigger i need to have and whats the synatx and minimum permissions needed to create it , now i am security admin.
Hello.
Is it possible to create a trigger to insert, update and delete data from all tables of current database?
Something like this:
CREATE TRIGGER myTrigger
ON
FOR INSERT, UPDATE, DELETE
AS IF (@@ERROR 0)
BEGIN
INSERT into #tableSomething VALUES (1)
END
I’m using SQL Server 2008.
Thank you
TAP
Great stuff
Do you have any way to get alerted (paged/emailed) whenever a database has an Auto_grow?
Hello sir,
If I created a ddl trigger. How can i drop it??
Display all Triggers
SELECT * FROM sys.server_triggers
Drop a Trigger
DROP TRIGGER [DROP_Your_Trigger] ON ALL Server
or see this link for DROP TRIGGER SYNTAX
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-trigger-transact-sql?view=sql-server-2017
Use drop trigger trigger_name
If your trigger was created ON DATABASE, then you need to DROP TRIGGER trigger_name ON DATABASE, and if it was created ON SERVER then . . .well I’m sure you can work it out!
I m working on Trigger which will prevent database users from executing update statement on table.
Sir, I Want to hide error 3609 after execution of Trigger.
while trying to drop stored procs, error:
Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Kindly help me….
I m beginner in triggers i got this error..
plz help me..
thank u..
CREATE TRIGGER preventDB
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT ‘HELLO’
ROLLBACK
GO
error is…
Incorrect syntax near the keyword ‘DATABASE’.
Dear Pinal Dave,
Where this magic tables are created?
When they will created?
When they will dropped?
How long it will be used?
Thanks,
Prakash.
Can we access the view after the table on which it has been created is dropped ?
I created a DDL trigger which prevents adding new file to the database , After creating the trigger i try to alter the database by adding a new file , The output message says ” The transaction ended in trigger , the batch has been aborted” But still i see the new file added to the database , please share your valuable suggestion to make my trigger work.
DDL Trigger to Prevent adding new file to database:
CREATE TRIGGER prevent_addfile
ON ALL SERVER
FOR ALTER_DATABASE
AS
BEGIN
PRINT ‘ALTER_DATABASE Issued.’
DECLARE @EventData XML,
@TSQL varchar(2000)
SET @EventData=EVENTDATA()
–SELECT @TSQL = @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’, ‘nvarchar(max)’)
–PRINT @TSQL
–IF CHARINDEX (‘ADD’,@TSQL) > 0
–if( isnull(charindex(‘ADD’,@TSQL), 0) > 0 )
IF @EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(50)’) LIKE ‘%dba%’
AND @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’, ‘nvarchar(max)’) LIKE ‘%ADD%’
RAISERROR (‘dbfile cannot be modified’, 16, 1)
ROLLBACK
END
GO
Output message after triggering the Alter database command on the “dba” database
ALTER_DATABASE Issued.
Msg 50000, Level 16, State 1, Procedure prevent_addfile, Line 21
dbfile cannot be modified
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.