Triggers are stored programs, whenever any action in the table such as: - insertion, deletion, update is done, the triggers are executed automatically. Trigger can create for DDL statement means when a DDL statement is perform like as – create, drop, alter then trigger is automatically called.
Triggers cannot be called or invoked because whenever a DDL and DML statement is executed, the trigger is executed automatically.
Syntax-
CREATE TRIGGER trigger_name ON table_name
AFTER INSERT | AFTER DELETE | AFTER UPDATE
AS BEGIN
INSERT INTO new_created_table
END;
Example:
Create a table ,
CREATE TABLE Employees(
EmpId int not null,
EmpName varchar(256),
Gender varchar(15),
Salery money
);
Inserted record in ‘Employees’ table as-
Create a another table to store transaction records like the records of INSERT, DELETE, UPDATE on the ‘Employees’ table
CREAE TABLE trgTest(
ID int not null,
Name varchar(256),
Gender varchar(15),
Salery money
);
Now Create a Trigger:
CREATE TRIGGER trgAfterInsert ON Employees
FOR INSERT AS
DECLARE @ID INT;
DECLARE @Name varchar(256);
DECLARE @Gender varchar(15);
DECLARE @Salery money;
SELECT @ID=i.EmpId FROM inserted i;
SELECT @Name=i.EmpName FROM inserted i;
SELECT @Gender=i.Gender FROM inserted i;
SELECT @Salery=i.Salery FROM inserted i;
insert into trgTest(
ID, Name, Gender, Salery)
Values(@ID, @Name, @Gender, @Salery);
print 'After insert trigger fired';
The trigger is fired automatically when record is insert into ‘Employees’ table.
Other example of creating a trigger is as below-
1- Create a table ‘DemoTrigger’.
2- Create another table ‘TestTrigger’.
3- Create trigger and apply it on First created table as is given in below SQL statement
4- Select all columns from ‘DemoTrigger’ table and insert into ‘TestTrigger’ table through trigger.
5- When you insert any record into DemoTrigger table then trigger send that record into second table ‘TestTrigger’ automatically.
CREATE TRIGGER trgTestTrigger ON DemoTrigger
FOR INSERT AS
DECLARE @ID INT;
DECLARE @Name varchar(256);
DECLARE @Gender varchar(15);
DECLARE @Salary money;
SELECT @ID =i.ID FROM inserted i;
SELECT @Name =i.trgName FROM inserted i;
SELECT @Gender =i.trgGender FROM inserted i;
SELECT @Salary =i.trgSalary FROM inserted i;
INSERT INTO TestTrigger(ID, Name, Gender, Salary)
Values(@ID, @Name, @Gender, @Salary);
Here insert a record into ‘DemoTrigger’ like as-
insert into DemoTrigger
Values(101,'Roman Reigns','Male',150000);
When record is inserted into ‘DemoTrigger’ then its trigger automatically fired and insert that record into other table ‘TestTrigger’.
Above table is 'DemoTriger' and the below table is 'TestTrigger'.
Types of Trigger:
There are two types of triggers used in SQL Server-
1- DDL trigger (Data Definition Language).
2- DML trigger (Data Manipulation Language).
DDL trigger:
In the SQL Server you can create trigger for DDL statement. If when you perform DDL commands then the trigger is automatically called or invoked.
The DDL command is like- CREATE, ALTER, DROP.
DML trigger:
In the SQL Server you can create trigger for DML statement. When you perform a DML command then the trigger is invoked automatically.
The DML command is like – INSERT, SELECT, DELETE, UPDATE etc.
