07 - Triggers (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          View

-          SP

-          Functions

-          Index

-          Triggers

 

 

 

Triggers

 

·          There are two types of triggers,

 

AFTER 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.

 

Operation	Inserted Table	Deleted Table
INSERT	New Rows	-
UPDATE	New Rows	Old Rows
DELETE	-	Deleted Rows

 

·          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]

AS

BEGIN

<transact_sql_statements>

END

GO

 

 

-          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 AS BEGIN UPDATE Inventory SET iTotalBooks = iTotalBooks + (SELECT COUNT(*) FROM INSERTED) END GO

Creating an INSTEAD OF trigger   CREATE TRIGGER trg_BookAuthor_INSTEAD ON vBookAuthors INSTEAD OF INSERT AS INSERT Books (vchBookName, iAuthorId) SELECT inserted.vchBookName, inserted.iAuthorId FROM INSERTED   INSERT Authors (iAuthorId, vchLastName, vchFirstName) SELECT inserted.iAuthorId, inserted.vchLastName, nserted.vchFirstName FROM INSERTED   GO

 

·          Triggers can be enabled or disabled

 

ALTER TABLE Books

DISABLE TRIGGER ALL

 

 

Hope this helps.

 

Thanks & Regards,

Arun Manglick || Tech Lead