SQL SERVER – 2005 – Server and Database Level DDL Triggers Examples and Explanation

SQL SERVER - 2005 - Server and Database Level DDL Triggers Examples and Explanation trigger1 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.

Solarwinds

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

Solarwinds
, , ,
Previous Post
SQL SERVER – UDF – Get the Day of the Week Function
Next Post
SQL SERVER – Data Warehousing Interview Questions and Answers – Introduction

Related Posts

49 Comments. Leave new

  • Hello Niranjan,

    Create INSTEAD OF trigger without any DML statement.

    Regards,
    Pinal Dave

    Reply
  • hello sir,

    can we pass parameter to trigger

    Reply
  • 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’.””””””

    Reply
    • 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

      Reply
  • 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

    Reply
    • 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

      Reply
  • 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

    Reply
  • minakshi bhardwaj
    October 11, 2010 10:03 pm

    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 .

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Great stuff

    Do you have any way to get alerted (paged/emailed) whenever a database has an Auto_grow?

    Reply
  • Hello sir,
    If I created a ddl trigger. How can i drop it??

    Reply
  • 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.

    Reply
  • 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….

    Reply
  • 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’.

    Reply
  • 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.

    Reply
  • shreyansh sahay
    May 13, 2018 1:59 pm

    Can we access the view after the table on which it has been created is dropped ?

    Reply

Leave a Reply

Menu