Here we'll cover below –
· There are two types of triggers,
Execute after the data modification has been completed against the table.
INSTEAD OF triggers
· Execute instead of the original data modification.
· INSTEAD OF triggers are allowed for both tables and views
· Multiple triggers can be defined for one table. The order in which they are fired can be configured using the sp_settriggerorder stored procedure
· The Nested Triggers server option will determine if triggers can cause other triggers to fire (cascading triggers). If enabled, triggers can cascade in a chain of up to 32 firings.
· SQL Server creates two "virtual" tables specifically for triggers, the Deleted and Inserted tables. These two tables capture before and after pictures of the modified data.
· Points to Remember
o Triggers should be written to run quickly.
o Constraints usually run faster than a trigger
o You can only create triggers on table/viewss that you own
o If cascading referential integrity for DELETE or UPDATE is defined for the base table, you cannot define an INSTEAD OF DELETE or INSTEAD OF UPDATE on that table.
CREATE TRIGGER <trigger_name> ON <table_name or view_name>
[ WITH ENCRYPTION ]
[ (FOR or AFTER) DELETE, INSERT, UPDATE | INSTEAD OF DELETE, INSERT, UPDATE ]
[WITH APPEND] [NOT FOR REPLICATION]
- You can use FOR or AFTER interchangeably and on tables.
- Specifies that the trigger should not be executed when a replication modification is performed against the table.
Creating an AFTER trigger
CREATE TRIGGER trg_BookAudit ON Books FOR INSERT
SET iTotalBooks = iTotalBooks + (SELECT COUNT(*) FROM INSERTED)
Creating an INSTEAD OF trigger
CREATE TRIGGER trg_BookAuthor_INSTEAD ON vBookAuthors INSTEAD OF INSERT
INSERT Books (vchBookName, iAuthorId)
SELECT inserted.vchBookName, inserted.iAuthorId FROM INSERTED
INSERT Authors (iAuthorId, vchLastName, vchFirstName)
SELECT inserted.iAuthorId, inserted.vchLastName, nserted.vchFirstName
· Triggers can be enabled or disabled
ALTER TABLE Books
DISABLE TRIGGER ALL
Hope this helps.
Thanks & Regards,
Arun Manglick || Tech Lead