Trigger
A trigger is a special kind of stored procedure.
which automatically on the happing of event like before after insert,update or delete.
Syntax:
create trigger trigger name on table name
after insert/delete/update
as
query to be executed
go
What is a trigger?
Database are set of commands that get executed
when an event (Before Insert, After Insert, On Update, On delete of a row)
occurs on a table, views.
TRIGGERS IN SQL:
What is ‘TRIGGER’:
1
A trigger is a Database object just like a
stored procedure or we can say it is a special kind of Stored procedure which
fires after (/before) a specified language event executes.
2
The main difference between a trigger and a
stored procedure is that the former is attached to a table or view and is fired
only when an INSERT, UPDATE, and/or DELETE occurs, while a stored
procedure executes at any time when it is called.
Types of Triggers:
1) DML
Triggers
---- INSTEAD OF Triggers
---- AFTER Triggers
2) DDL
Triggers
3) CLR
Triggers
1) DML TRIGGERS:
1
DML Triggers are fired when a Data Manipulation
Language (DML) event takes place. These are attached to a Table or View and are
fired only when an INSERT, UPDATE and/or DELETE event occurs.
2
The trigger and the statement that fires it are
treated as a single transaction.
--AFTER TRIGGER:
AFTER triggers are executed after the action of the INSERT,
UPDATE, or DELETE statement is
performed.
E.g.:
CREATE TABLE
SAKTHI2(User_ID
int IDENTITY, User_Name varchar(30), Type varchar(10))
Go
CREATE TRIGGER
tr_INSERT
ON SAKTHI2
FOR INSERT
AS
PRINT GETDATE()
Go
INSERT SAKTHI2 (User_Name, Type) VALUES ('ASHOK', 'EMPLOYEE')
SELECT*FROM SAKTHI2
--INSTEAD OF TRIGGERS:
INSTEAD-OF triggers are objects that will execute instead
of data manipulation statements in the database engine.
E.g.: CREATE TABLE
Mayank (Name varchar(32))
GO
CREATE TRIGGER tr_mayank ON
Mayank
INSTEAD OF DELETE
AS
PRINT
'Sorry - you cannot delete this data'
GO
INSERT Mayank
SELECT 'Cannot' union
SELECT 'Delete' union
SELECT 'Me'
GO
DELETE FROM
Mayank
GO
SELECT * FROM Mayank