How to create and use trigger in SQL?
by Abdul Rehman • November 12, 2015 • Microsoft SQL Server • 0 Comments
A trigger is used to perform certain action on the table like insertion, deletion or updation of data. It is database object which is bound to a table and is executed automatically. You can implicitly invoke trigger.
Types of Triggers
There are three action query types that you use in SQL which are:
- INSERT
- UPDATE
- DELETE
In this article you will learn trigger, types of trigger and how to create and used trigger in SQL. The only focus on insert trigger in this article.
Create Table:
Create the table name Student and name the column of table is ID, Name and Percentage. After this inset data on student table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE Student ( ID INT Identity, Name Varchar(100), Percentage Decimal (10,2) ) INSERT INTO Student VALUES ('Tom',90.2); INSERT INTO Student VALUES (Rowd,85.6); INSERT INTO Student VALUES ('John',80); INSERT INTO Student VALUES (Henry,75); INSERT INTO Student VALUES ('Maria',60.9); |
Insert Trigger
This trigger is fired after an INSERT on the table. Let’s create the trigger as:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE TRIGGER trgAfterInsert ON [dbo].[Studetn] FOR INSERT AS declare @id int; declare @name varchar(100); declare @per decimal(10,2); declare @audit_action varchar(100); select @empid=i.ID from inserted i; select @empname=i.Name from inserted i; select @empsal=i.Percentage from inserted i; set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Test_Audit (ID,Name,Percentage,Audit_Action,Audit_Timestamp) values(@empid,@empname,@empsal,@audit_action,getdate()); PRINT 'AFTER INSERT trigger fired.' GO |
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table.
