A trigger is a special kind of a store procedure that fire
implicitly when certain event occurs in database server. We can’t invoke
triggers explicitly. It can be used to perform administrative tasks in database
such as regulating database operation.
Types of TRIGGER:
1. DDL TRIGGER:
DDL
triggers execute in response to a variety of Data Definition Language (DDL)
events such as CREATE, ALTER,
DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS statements and certain system stored procedures
that perform DDL like action. DDL triggers do not fire in
response to events that affect local or global temporary tables and stored
procedures.
- DDL triggers do not support INSTEAD OF feature.
- DDL triggers do not have feature of inserted and deleted
tables like DML triggers.
Example :
CREATE TRIGGER PreventDropTable
ON DATABASE
AS
PRINT 'Drop table action not allowed.'
ROLLBACK;
GO
Droping table to test trigger action
DROP TABLE [EMPLOYEE]; It will not drop table due to triggering action
We can disable or enable trigger
--Disabling database trigger
DISABLE TRIGGER PreventDropTable ON
DATABASE;
Types of TRIGGER:
Droping table to test trigger action
DROP TABLE [EMPLOYEE]; It will not drop table due to triggering action
We can disable or enable trigger
--Disabling database trigger
DISABLE TRIGGER PreventDropTable ON DATABASE;
--Enabling database trigger
ENABLE TRIGGER
PreventDropTable ON DATABASE;
2. DML TRIGGER:
DML
trigger is performed on DML operation (INSERT, UPDATE, DELETE) to enforce rule
and regulation on table. Trigger can prevent incorrect, unauthorized,
inconsistent changes to data.
A). AFTER
TRIGGER (FOR TRIGGER)
After triggers run after INSERT, UPDATE or DELETE operation
on table. They do not supported for
views.
AFTER TRIGGERS can be classified further into
three types as:
i). AFTER INSERT Trigger
This trigger is fired after
an INSERT on the table. Let’s create the trigger as:-
--Creating a sample table
CREATE TABLE
empProfile
(id INT IDENTITY(1,1),
name VARCHAR(50),
gender VARCHAR(6),
create_date DATETIME,
update_date DATETIME
)
--Creating trigger to automatic update log
CREATE TRIGGER
setCreateDate ON [dbo].[empProfile]
FOR
INSERT
AS
--AUTOMATIC populate create_date
UPDATE empProfile SET
create_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
PRINT 'AFTER INSERT trigger
fired.'
GO
Inserting value in database
to test trigger action
INSERT INTO
empProfile (name,gender)
SELECT 'MDF', 'MALE'
UNION ALL
SELECT 'ZTE', 'FEMALE'
UNION ALL
SELECT 'John', 'MALE'
UNION ALL
SELECT 'Abc', 'MALE'
SELECT * FROM empProfile
ii). AFTER UPDATE Trigger
This trigger is fired after
an update on the table.
Example:
--Creating trigger to automatic update log
CREATE TRIGGER
setUpdateDate ON [dbo].[empProfile]
FOR
UPDATE
AS
--AUTOMATIC populate create_date
UPDATE empProfile SET
update_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
PRINT 'AFTER Update trigger
fired.'
GO
UPDATE empProfile SET name='XYZ'
WHERE id=1
UPDATE empProfile SET name='MDF'
WHERE id=3
iii). AFTER DELETE Trigger
This trigger is fired after a
delete on the table.
Example:
CREATE TRIGGER
onDeleteTest ON [dbo].[empProfile]
AFTER
DELETE
AS
PRINT 'AFTER DELETE trigger
fired.'
GO
Testing trigger action
DELETE FROM
empProfile WHERE ID=2
A). Instead Of Triggers
Instead of trigger override
default action of DML operation on table or view. After defining INSTEAD OF
trigger if we want to delete a row then Delete operation trying to delete but
actually row will not get deleted unless you issue another delete instruction
with in trigger.
Advantage of INSTEAD OF
trigger:
- It can enable views that
would not be updatable to support updates. A view based on multiple base tables
must use an INSTEAD OF trigger to support inserts, updates, and deletes that
reference data in more than one table.
- It can enable you to code
logic that can reject DML operation according to condition.
- It
enable to ignoring parts of a batch.
Note: INSTEAD
OF DELETE and INSTEAD OF UPDATE trigger cannot be defined on table if a table has foreign key
defined by using DELETE or UPDATE cascading action then INSTEAD OF DELETE and
INSTEAD OF UPDATE cannot be defined.
INSTEAD OF trigger can be
classified further into three types:
i). INSTEAD OF INSERT Trigger.
Example :
CREATE TRIGGER
onInsertTest ON [dbo].[empProfile]
INSTEAD OF INSERT
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can
not INSERT FEMALE employee',16,1);
ELSE
INSERT INTO
empProfile (name, gender)
SELECT name,
gender FROM inserted
GO
Testing trigger action
INSERT INTO
empProfile (name, gender)
SELECT 'DEVA', 'FEMALE'
ii). INSTEAD OF UPDATE Trigger.
Example :
CREATE TRIGGER
onUpdateTest ON [dbo].[empProfile]
INSTEAD OF UPDATE
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can
not update FEMALE employee',16,1);
ELSE
UPDATE empProfile SET name =
(SELECT
name FROM inserted)
WHERE ID =(SELECT id FROM
inserted)
GO
Testing trigger action
UPDATE empProfile set NAME='INDRA'
WHERE ID = 2
iii). INSTEAD OF DELETE Trigger.
Example :
CREATE TRIGGER
onDeleteTest ON [dbo].[empProfile]
INSTEAD OF DELETE
AS
IF EXISTS( SELECT 1 FROM deleted
WHERE gender ='FEMALE')
RAISERROR('Can
not delete FEMALE employee',16,1);
ELSE
DELETE FROM
empProfile WHERE ID IN (SELECT D.ID FROM deleted D)
GO
Testing trigger action
DELETE FROM
empProfile WHERE ID IN (3,4)
3. LOGON TRIGGER
Logon triggers fire in response to the LOGON
event that is raised when a user sessions is being established.
Example :
CREATE TRIGGER logging_logon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logHistoryTbl (sys_user, loginTime)
SELECT SYSTEM_USER,GETDATE()
END
GO
--Enabling database trigger
AFTER TRIGGERS can be classified further into three types as:
--Creating trigger to automatic update log
i). INSTEAD OF INSERT Trigger.
Example :
Testing trigger action
CREATE TRIGGER logging_logon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logHistoryTbl (sys_user, loginTime)
SELECT SYSTEM_USER,GETDATE()
END
GO