Scripting triggers in Microsoft SQL Server

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…

Check the [Script all objects in the selected database]

Set the [Script Triggers = True]

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