TRIGGERS
* It is also a SP but executed automatically when a DDL or DML command is executed.
* The statement, responsible for the invocation of a
trigger is called triggering
statement.
* Triggering statements
*
CREATE, ALTER, DROP
*
INSERT, UPDATE, DELETE
*
Login event
* It is an extra instruction to db engine.
* S.P is executed manually but trigger is executed
automatically.
* S.P can take parameters but trigger cannot parameter.
Advantages
* To implement complex business logic, which cannot be
possible with the help of constraints?
* To implement automatic background process.
* To control the execution by the db engine.
* We can perform some extra task by the db engine along
with the operators command.
* We can perform some alternate task when the command is
issued by the operator.
* To audit the changes.
* To monitor the DDL commands.
* To restrict to perform some task on particular days or
time or by a particular users.
* To provide high security for data.
Syn:
CREATE
TRIGGER <TRIGGERNAME>
ON
<tname>/<viewname>/database/Server
FOR/INSTEAD
OF commands
as
BEGIN
---
---
END
Types of
Triggers
*
For/After Triggers
*
DML
*
DDL
(introduced in SS2005)
*
Instead Of ,,
*
Logon Tiggers (introduced in sp2)
1. For /After
Triggers
*
First triggering st is executed then trigger is fired.
* If
we want to undo the changes made by trigger st then we have to ROLLBACK.
* To
perform some extra task along with triggering statement.
Execution Plan
*
Triggering statement is
*
Parses (compiling & syntax)
*
Resolves (Verifying cols, tables etc
used in the query)
*
Optimizes (Generates execution plan)
*
It prepares magic table(s)
*
Triggering statement is executed.
*
Trigger body is fired.
Ex1: Trigger
to prevent deletions on sunday on Accs_Ledger table.
CREATE TRIGGER DAY_TRG
ON accs_ledger
FOR DELETE
AS
BEGIN
if
datename(dw,getdate())='Sunday'
Begin
rollback
tran
Print
'Cannot delete on this day'
End
END-- END OF TRIGGER
--step2: Testing trigger
* Change system date to Sunday.
* Run delete command
delete
from accs_ledger
* Trigger is fired and it throws error
Ex: Emp_Tbl
empid ename sal deptno
1 --- 9000 10
Update
emp_tbl set sal=9000 where empid=1
When
salary is updated we need to store the details in another table
sal_updated
empid
oldsal date_updated
When
update command is executed in the background two tables are created.
*
deleted
*
inserted
Deleted Inserted
empid ename sal deptno empid ename sal deptno
1 --- 8000 10 1 --- 9000 10
*
These 2 tables are called as magic tables present in SQL Server.
1) Deleted table
i)
It is created while working with delete or update commands.
ii)
It consists of deleted rows.
iii)
Its structure is similar to base table on which triggering statement is
executed.
2) inserted table
i)
Another magic table, created while working with insert or update commands.
ii)
It consists of new row.
iii)
Its structure is similar to base table.
Step-1
create table sal_updated (empid int,oldsal
money,date_updated datetime)
Step-2
create trigger sal_trg
on emps
for update
as
begin
if
update(sal)
insert
sal_updated select empid,sal,getdate() from deleted
END --end of the trigger
Step3
update emps set sal=10000 where empid=1
select * from sal_updated
select * from emps
Ex: Create a
trigger which prevents to insert more than 3 empls in 20 dept.
Step1
create trigger dept20_trg
on emps
for insert,update
as
begin
declare
@dno int
select
@dno=deptno from inserted
if
@dno=20
begin
if(select
count(*) from emps where deptno=20)>3
rollback
raiserror('More
than 3 empls are not allowed in 20 th dept',15,16)
end
end
Step 2
insert emps(empid,sal,deptno) values(100,5000,20)
Ex:
Create a trigger which prevents insertion of rows with the salary
>50000.
Step 1
create trigger sal_trg
on emps
for insert
as
begin
declare @sal money
select @sal=sal from inserted
if @sal >50000
begin
rollback
raiserror('salary greater than 50000 not accepted',12,16)
end
end
Step 2
insert emps(empid,sal) values(50,55000)
Ex: Create a
trigger to prevent deletion of 10 dept empls whose sal<10000.
create trigger
dept10_trg on emps
for delete
as
begin
declare
@dno int , @sal money
select @dno=deptno from deleted
select @sal=sal from deleted
if @dno=10 and @sal<10000
begin
rollback
raiserror('dept10 employees with salless than 10000 cant
be deleted',15,16)
end
end
Step2
delete from emps where empid=1
Ex:
Accs_ledger deposits
acno ah_name amt_bal branch acno amt date_deposited
1 --- 50000 Ampt 1 5000 ---
Create a trigger
to update amt_bal of accs_ledger table when a record
is inserted into deposits table.
Step 1
create trigger deposits_trg
on deposits
for insert
as
set nocount on
begin
declare
@acno int,@amt money
select
@acno=acno from inserted
select
@amt=amt from inserted
update
accs_ledger set amt_bal=amt_bal+@amt where acno=@acno
if
@@rowcount=1
print
'Transaction Success'
else
Print
'Transaction Failed'
end
Step 2
Insert deposits values(1,500,getdate())
Step 3
select * from accs_ledger
2. DDL Triggers
*
These triggers are fired when a DDL command is executed.
* We
can monitor DDL activities on database level or object level.
* To
provide security for database objects.
*
Complete event details we can capture by using "EVENTDATA()".
*
EVENTDATA() returns the details in XML format.
<EVENT_INSTANCE>
<EventType>type</EventType>
<PostTime>date-time</PostTime>
<SPID>spid</SPID>
<ServerName>name</ServerName>
<LoginName>name</LoginName>
<UserName>name</UserName>
<DatabaseName>name</DatabaseName>
<SchemaName>name</SchemaName>
<ObjectName>name</ObjectName>
<ObjectType>type</ObjectType>
<TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>
Ex: Create
a trigger which prevents alter,drop on tables.
Step1
CREATE TRIGGER ddl_trg
ON DATABASE
FOR ALTER_TABLE,DROP_TABLE
AS
BEGIN
ROLLBACK
TRAN
RAISERROR('You
cannot drop or alter tables',15,16)
END
STEP2
drop table emps
Ex: Create a
trigger which stores the details when the user works with any ddl command.
Step3: creating table to hold the ddl events.
USE Test;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User
nvarchar(100), Event nvarchar(100), TSQL
nvarchar(2000));
GO
Step4: creating trigger which inserts rows into
ddl_log table.
CREATE TRIGGER log_trg
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime,
DB_User, Event, TSQL)
VALUES
(GETDATE(),CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
Step3: Testing
trigger
Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
Step4: Viewing
the above event details
SELECT * FROM ddl_log ;
GO
Step5:
Dropping trigger
Drop the trigger.
DROP TRIGGER log_trg ON DATABASE
GO
Step 6:
Dropping table ddl_log.
DROP TABLE ddl_log
GO
Ex: Displaying the
event when create command is executed.
Step1:
Creating trigger
USE Test
GO
CREATE TRIGGER safety ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE
TABLE Issued.'
SELECT
EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
RAISERROR ('New
tables cannot be created in this database.', 16, 1)
ROLLBACK
;
GO
Step2:
Test the trigger.
CREATE TABLE NewTable (Column1 int);
GO
Step3: Drop
the trigger.
DROP TRIGGER safety ON DATABASE
GO
3. INSTEAD OF
Triggers
* To perform some alternate task when a command is issued
by the operator.
* Here triggering statement is not executed only trigger
body is fired.
* We can update complex views with these triggers.
Ex:- Create
a trigger which prevents insert on emps table.
Step1
CREATE TRIGGER Emps_trg
ON EMPS
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
RAISERROR('You
cannot insert rows',14,17)
END
Step 2
INSERT Emps VALUES(10,'Kim',5000,10)
Step 3
SELECT * FROM Emps --
Record is not inserted
Ex2: Create a
trigger to update complex view, "emp_vw".
Step1
CREATE VIEW emp_vw
AS
SELECT empid,sal as M_sal, sal*12 as A_sal FROM emps
Step2
INSERT emp_vw VALUES(10,1000,12000)
Step3
CREATE TRIGGER INS_Trg
ON emp_vw
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
INSERT
emp(empid,sal) SELECT empid,M_sal FROM INSERTED
END
Step4
INSERT emp_vw VALUES(10,1000,12000) -- inserted successfully.
Step5:
SELECT * FROM emps
Step6:
SELECT * FROM emp_vw
4. Logon
Triggers
* These triggers were introduced in service pack2 on SQL
Server 2005.
* Once the login is authenticated successfully then these
triggers are fired.
* We can use these triggers to perform something like
storing details etc, when the user logon
to server.
* There is no triggering statement.
Create a trigger
which doesn't allow the user login_test to take more than 3 sessions.
Step1: Creating login
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = 'hyd@123'
GO
Step2: Granting permission
GRANT VIEW SERVER STATE TO login_test;
GO
Step3: Creating trigger
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT
COUNT(*) FROM sys.dm_exec_sessions
WHERE
is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
To view
triggers present on table
sp_helptrigger <tname>
ex
sp_helptrigger emp
To Disable Or
Enable Trigger
alter table <tname> disable/enable trigger <triggerName>
To drop trigger
drop trigger <triggerName> -- For table level
drop trigger <triggerName> on database -- For database level
drop trigger <triggerName> on all server -- For server level
To view the
trigger definition
sp_helptext <triggerName>
