Hello Friends,
In this article, we will learn triggers in sql, types of triggers in sql and Magic Tables.
A trigger can define as a database object simply a special type of stored procedure, which is attached with another type of object with in the database and invoked automatically when an event related to that object occurs in the database server. Triggers are used to apply some operations automatically when a particular event raised with in database.
Types of Triggers
1) Data Definition Language (DDL) Triggers
2) Data Manipulation Language (DML) Triggers
3) Logon Triggers
1) Data Definition Language (DDL) Triggers
DDL triggers run in response data definition language (DDL) events such as CREATE, ALTER, and DROP statements and certain system stored procedures that perform DDL-like operations.
Syntax:
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE or UPDATE statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH
[ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
Following example creates a trigger to rollback tables for CREATE, ALTER AND DROP.
CREATE TRIGGER trg_Rollback_Tables
ON DATABASE
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
RAISERROR ('you can not create ,drop and alter table in this database',10, 1)
ROLLBACK;
Go
We can drop DDL trigger as follows.
DROP TRIGGER trg_Rollback_Tables
ON DATABASE;
Go
Following example will create server-scoped DDL trigger.
CREATE TRIGGER trig_AllDbServer
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
Go
Droping server-scoped DDL trigger.
DROP TRIGGER trig_AllDbServer
ON ALL SERVER;
Go
2) Data Manipulation Language (DML) Triggers
DML triggers run when a user tries to modify data through a data manipulation language (DML) events such as INSERT, UPDATE, or DELETE statements on a table or view. DML triggers use the deleted and inserted logical (conceptual) tables. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. These are also called Magic Tables.
Syntax:
-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH
[ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name
Most of the time we uses DDL trigger. We can use them in three modes FOR, AFTER and INSTEAD OF with two magical tables i.e inserted and deleted.
To understand lets create a table with some rows.
CREATE TABLE dbo.tbStudent
(
StudentID INT IDENTITY PRIMARY KEY,
StudentName VARCHAR(200),
ClassID INT,
SectionID INT,
SessionID int
)
Go
INSERT INTO dbo.tbStudent
SELECT 'Jack',1,1,1
UNION
SELECT 'Maria',2,1,1
UNION
SELECT 'Wiliam',1,1,1
UNION
SELECT 'Rock',2,1,1
UNION
SELECT 'Marina',1,1,1
UNION
SELECT 'Harish',2,1,1
UNION
SELECT 'Jhon',1,1,1
UNION
SELECT 'Raidu',2,1,1
Go
SELECT * FROM dbo.tbStudent
Go
OUTPUT: StudentID StudentName ClassID SectionID SessionID ----------- ------------------ ----------- ----------- ----------- 37 Harish 2 1 1 38 Jack 1 1 1 39 Jhon 1 1 1 40 Maria 2 1 1 41 Marina 1 1 1 42 Raidu 2 1 1 43 Rock 2 1 1 44 Wiliam 1 1 1 (8 rows affected)
Trigger with FOR:
Lets create a trigger trg_Student for dbo.tbStudent with INSERT, UPDATE AND DELETE statements.
Here we are using FOR it means trigger will invoke with DML commands simultaneously.
CREATE TRIGGER dbo.trg_Student
ON dbo.tbStudent
FOR INSERT,UPDATE,DELETE
AS BEGIN
PRINT 'Magic Table: inserted'
SELECT * FROM inserted
PRINT 'Magic Table: deleted'
SELECT * FROM deleted
END
Go
When we insert new record in a table, New row also created into inserted table as shown in trigger below.
Lets insert one row in table dbo.tbStudent
TRIGGER OUTPUT: Magic Table: inserted StudentID StudentName ClassID SectionID SessionID ----------- -------------------- ----------- ----------- ----------- 46 Polard 1 1 1 (1 row affected) Magic Table: deleted StudentID StudentName ClassID SectionID SessionID ----------- -------------------- ----------- ----------- ----------- (0 rows affected) (1 row affected)
When we update a record in a table, old values deletes and move into deleted table. And changed values are inserted into both inserted and respective table.
Lets update a row in table dbo.tbStudent having StudentID 37
UPDATE dbo.tbStudent
SET StudentName = 'Hairer'
WHERE StudentID = 37
Go
TRIGGER OUTPUT: Magic Table: inserted StudentID StudentName ClassID SectionID SessionID ----------- -------------------- ----------- ----------- ----------- 37 Hairer 2 1 1 (1 row affected) Magic Table: deleted StudentID StudentName ClassID SectionID SessionID ----------- -------------------- ----------- ----------- ----------- 37 Harish 2 1 1 (1 row affected) (1 row affected)
When we delete record in a table, old values shifts to deleted table as shown in trigger below.
Lets delete a row in table dbo.tbStudent having StudentID 37
DELETE FROM dbo.tbStudent
WHERE StudentID = 37
Go
TRIGGER OUTPUT: Magic Table: inserted StudentID StudentName ClassID SectionID SessionID ----------- ----------------------- ----------- ----------- ----------- (0 rows affected) Magic Table: deleted StudentID StudentName ClassID SectionID SessionID ----------- ----------------------- ----------- ----------- ----------- 37 Hairer 2 1 1 (1 row affected) (1 row affected)
AFTER Trigger:
AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement. We can use AFTER Trigger for, if we want to apply some operations after INSERT, UPDATE and DELETE DML operations.
ALTER Trigger trg_Student replace FOR With AFTER
ALTER TRIGGER dbo.trg_Student
ON dbo.tbStudent
AFTER INSERT,UPDATE,DELETE
AS BEGIN
PRINT 'Table: tbStudent'
SELECT * FROM dbo.tbStudent
WHERE StudentID = 38
PRINT 'Magic Table: inserted'
SELECT * FROM inserted
PRINT 'Magic Table: deleted'
SELECT * FROM deleted
END
Go
INSTEAD OF Trigger:
It will tell the database engine to execute the trigger instead of executing the statement.
ALTER Trigger trg_Student for INSTEAD OF trigger.
ALTER TRIGGER dbo.trg_Student
ON dbo.tbStudent
INSTEAD OF INSERT,UPDATE,DELETE
AS BEGIN
PRINT 'Magic Table: inserted'
SELECT * FROM inserted
PRINT 'Magic Table: deleted'
SELECT * FROM deleted
END
Go
When we make any change in table trg_Student, it will not effected. Only statement inside trigger are executed.
Update tbStudent for StudentID = 38
UPDATE dbo.tbStudent
SET StudentName = 'None'
WHERE StudentID = 38
Go
TRIGGER OUTPUT: Magic Table: inserted StudentID StudentName ClassID SectionID SessionID ----------- ------------------- ----------- ----------- ----------- 38 None 1 1 1 (1 row affected) Magic Table: deleted StudentID StudentName ClassID SectionID SessionID ----------- ------------------- ----------- ----------- ----------- 38 Aashi 1 1 1 (1 row affected) (1 row affected)
Lets select student record for StudentID 38
SELECT * FROM dbo.tbStudent
WHERE StudentID = 38
Go
TRIGGER OUTPUT: StudentID StudentName ClassID SectionID SessionID ----------- ---------------------- ----------- ----------- ----------- 38 Aashi 1 1 1 (1 row affected)
No change is student record found.
3) Logon Triggers
Logon triggers fire in response to the LOGON event that's raised when a user's session is being established.
Syntax:
-- Trigger on a LOGON event (Logon Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH
[ ,...n ] ] { FOR| AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
Following t-sql query will show all the events, that cause a trigger to invoke.
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T ON T.object_id = TE.object_id
WHERE T.parent_class = 0 AND T.name = 'safety';
Go
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.