By default when you use the Script Wizard in Microsoft SQL Server Management Studio to script objects in a database, triggers are note included.

You can include triggers if you [Include all objects] and [Set triggers= true] during the Script Wizard.

 

To start the script wizard, right click the database that contains the triggers.

Choose Tasks > Generate Scripts…

 

image

 

Check the [Script all objects in the selected database]

 

image

 

Set the [Script Triggers = True]

image

 

image

 

image

 

image

 

The generate script will now contain all triggers in the database.

It will not generate drop statements for the triggers

So you should at [drop trigger] if it exists, before executing the generated create trigger statements:

For example the script wizard generates:

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO

Then you should change this to:

IF OBJECT_ID ('trd_Order', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER trd_Order
END

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.