Microsoft SQL Server have opportunity "Trigger" ."Trigger" is not like stored procedure or SQL Query , which need to execute ."Trigger" is an functionality object which fires himself when data in a table modified (Insert/Update/Delete). The firing will be determined by defination of trigger , when trigger will fired on Insert/Update/delete .Actually trigger is special kind of stored procedure automatically execute on event occur .We can create/alter trigger by simple DML statement.Trigger are generally execute to implement some business rules. , for example when I am creating a student records , it is obvious to create a login for that student.Here ,if we write a trigger which will create a login data when a new student inserted .Trigger will automatically fires when we add a new student and no need ti write extra logic or not extra event is required to create student login.Her is an exaple of trigger
A Practical Example
CREATE TRIGGER
myTrigger ON tbl_member
FOR INSERT,UPDATE, DELETE
AS
SELECT MEMBER_ID,MEMBER_NAME FROM
Inserted
SELECT MEMBER_ID,MEMBER_NAME FROM
deleted
--Wirte your
next code here
In teh above exaple , we have a table tbl_membe .We have creatde a trigger on tbl_member .We have define the trigger "FOR INSERT,UPDATE, DELETE" .That means , any operation INSERT,UPDATE, DELETE , the trigger will fired .Now , what happen when Insert, Update , Delete happen ? There is a concept of magic table .During Insert/Update/Delte two type of magic table are created .
- Insertde
- Deleted
Inserted tahble for Inserted value
Deleted table for deleted value
For updated , both inserted & deleted table works , first deleted then inserted.
Some points to remember
- Sql Server allow more than one trigger on a table
- When we have multiple trigger on a table , we can use ,"sp_settriggerorder" to set trigger firing order .For example trigger A will fire first , trigger B will fire next
CREATE Trigger
Mytrigger1 ON [dbo].[tbl_student]
FOR INSERT
AS
DECLARE @l_student_name VARCHAR(500),
@l_student_dob DATETIME,
@l_student_class INT,
@l_student_address VARCHAR(MAX)
SELECT @l_student_name=student_name,
@l_student_dob=student_dob,
@l_student_class=student_class,
@l_student_address=student_address
FROM INSERTED;
--Insert Audio
Table
INSERT INTO
tbl_student_audion
(
student_name,
student_dob,
student_class,
student_address
)
VALUES
(
@l_student_name,
@l_student_dob,
@l_student_class,
@l_student_address
)
GO
In the above exaple , a table named tbl_student hilds , students records , when we insert data into tbl_student , the same data will be copied into tbl_student_audit without any extra event for fireing.
How ever , trigger are basically two types
- After Trigger
- Instead of trigger
This type of trigger fires automatically after an insert/update/delete .After insert/After update/After Delete .This type of trigger fires only after main operation (Insert data).In the above example we can modify thus
CREATE Trigger
Mytrigger1 ON [dbo].[tbl_student]
AFTER INSERT
AS
DECLARE @l_student_name VARCHAR(500),
@l_student_dob DATETIME,
@l_student_class INT,
@l_student_address VARCHAR(MAX)
SELECT @l_student_name=student_name,
@l_student_dob=student_dob,
@l_student_class=student_class,
@l_student_address=student_address
FROM INSERTED;
--Insert Audio
Table
INSERT INTO
tbl_student_audion
(
student_name,
student_dob,
student_class,
student_address
)
VALUES
(
@l_student_name,
@l_student_dob,
@l_student_class,
@l_student_address
)
GO
In this process , first record are inserted in the tbl_student table and then data copied tbl_student_audit table. If we change it to "After Delete" , the first data will be deleted and the data will be inserted to tbl_student_audit table.After trigger are the only trigger to allow the same record that client application currently has locked(The operation that caused the trigger to fire earlier or first phase.
Instead of Insert
Instead of Insert trigger fires before the original operation Insert/Update/Delete .Actually it replace the original operation , if an instead of delete trigger exits on a table , when client delete record , the trigger code will be fired and control return to client.Now question is , when we will use Instead of Trigger ? When you modify the changes made by the client before actually posting update , Instead of Trigger used.
CREATE Trigger
Mytrigger1 ON [dbo].[tbl_student]
ALTER DELETE
AS
DECLARE @l_student_name VARCHAR(500),
@l_student_dob DATETIME,
@l_student_class INT,
@l_student_address VARCHAR(MAX)
SELECT @l_student_name=student_name,
@l_student_dob=student_dob,
@l_student_class=student_class,
@l_student_address=student_address
FROM DELETED;
--Insert Audio
Table
INSERT INTO
tbl_student_audion
(
student_name,
student_dob,
student_class,
student_address
)
VALUES
(
@l_student_name,
@l_student_dob,
@l_student_class,
@l_student_address
)
GO
Now , if i have a multiple trigger on a table , how to disable or on/off trigger ?
The syntax of trigger enable /disable is
ALTER TABLE
table_name DISABLE TRIGGER
tr_name
ALTER TABLE
table_name ENABLE TRIGGER
tr_name
For example , i want to disable "Mytrigger1" trigger from "tbl_student"
The sql will be
ALTER TABLE tbl_student ENABLE TRIGGER Mytrigger1
A Practical Example
--I am creating
two table test1 and test2
CREATE TABLE
tests1
(
id INT IDENTITY,
sname VARCHAR(50),
age INT
)
CREATE TABLE
tests2
(
id INT IDENTITY,
sname VARCHAR(50),
age INT
)
--I am inserting value to tests1
INSERT INTO
tests1(sname,age)
VALUES('Name1',12)
GO
INSERT INTO
tests1(sname,age)
VALUES('Name2',13)
--Now table test1 has 2 records and
test2 is empty
CREATE Trigger
Mytrigger1 ON [dbo].[tests1]
AFTER INSERT
AS
DECLARE @l_sname VARCHAR(50),
@l_age INT
--Initialised
variable
SELECT @l_sname='',
@l_age=NULL
--get value from
test1 into variable
SELECT @l_sname=sname,
@l_age=age
FROM INSERTED;
--insert data to
test2
INSERT INTO
tests2(sname,age)
SELECT @l_sname,@l_age
GO
---Now i am
again addtin data to tests1
INSERT INTO
tests1(sname,age)
VALUES('Name2',13)
---trigger will fires and put data to
tests2
SELECT *
FROM tests2
