--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
