TRIGGERS:
A trigger is also similar to a procedure but, which doesn't require to be called.
It will be fired when we perform any DML Operation on the table on which the trigger
is dependent.
A trigger is a user defined business rule that can be implemented on a table.
Triggering SQL Statement:
It is a statement which is applied on a table which has a trigger present on it.
Types of Triggers:
Before Trigger:
This trigger executes before triggering the SQL Statement. These are not
supported in SQL Server but , supported in Oracle.
After Trigger:
This trigger executes after triggering the SQL Statement. This can be defined
on only on table.
Instead Of Triggers:
These triggers fires without allowing the triggering SQL statement to executes and
executes their own code present in them. They can be defined on a view or a table.
Trigger Syntax:
CREATE TRIGGER Trigger_Name ON tname\ vname
[WITH Encryption] FOR \AFTER \INSTEAD OF
[INSERT][,UPDATE][,DELETE] <------------Event when the
trigger is fired
[NOT FOR REPLICATION]
As
Begin
--Statements
End
Replication:
It is a process of bringing two databases alike in terms of data and structure.
NOT for Replication:
If this option is specified the trigger will not fire at the time of Replication.
If not specified the trigger fires at time of replication also.
Examples:
Ex-1: Write a trigger on emp so that it will not allow us to perform
the manipulations before 9 AM and after 5 PM.
Sol: A trigger creates a transaction environment for us. We do not
require to use a begin transaction statement.
CREATE TRIGGER emp_trigger ON emp
AFTER INSERT,UPDATE,DELETE
AS
Begin
DECLARE @Dt int
Set @dt=DATEPART(HH,Getdate())
If (@Dt Not Between 9 AND 17 )
Begin
RAISEERROR('C annot Perform',16,1)
RollBack
End
End
Q) Define a trigger on emp so that the operation will be restricted for the specified
date in the holiday list table.
Q) Write a trigger on emp so that it checks for the integrity constraints thinking
that the constraints are available.
Q) W.A.T on the Dept Table to Convert the dept Name and location INTO & UpperCase
however they are inserted.
Magic Tables:
When we perform any DML Operation on a table, VALUES will be captured with in the
trigger INTO two magic tables.Those are Deleted and Inserted tables.
Inserted magic table:
If we perform an insert, the VALUES will be first taken INTO the inserted
table.
Insert INTO Dept VALUES(50,'Research','hyd')
Deleted magic table:
when we perform a delete operation the VALUES which are to be deleted will be captured
in the trigger with in the deleted table.
Delete from dept where dept no=10
When we perform a Update operation the system internally treats it as a delete and
insert statement, where the old VALUES can be captured in the deleted table and
the new VALUES can be captured in the insert table.
Update dept set deptno=60 where deptno=10
| (old)
|
| 10 |
Research
|
Hyd
|
| Deleted |
| (new)
|
| 60 |
Research
|
Hyd
|
| Inserted |
CREATE TRIGGER dept_trg_after ON dept
AFTER, INSERT, UPADTE //Instead of
AS
Begin
decalare @deptno int
DECLARE @dname VARCHAR(50)
DECLARE @dname VARCHAR(50)
decalare @loc VARCHAR(50)
select @deptno=deptno, @dname=dname ,@loc=loc from inserted
//inserted INTO dept VALUES(@deptno,upper(@dname),upper(@loc))
Update dept set dname=upper(@dname),loc=upper(@loc)
Where deptno=@deptno
COMMIT
End
Note:A table can contain number of after trigger defined on it
, per any event where as a table can contain only one instead of trigger per a event.
Ex-2:Write a trigger on emp table so that it will restrict the
updation thing preformed on employee no.
Sol:
CREATE TRIGGER emp_trg_upd ON emp
AFTER UPDATE
AS
Begin
If update(empno)
Begin
Raiseerror('emp cannot be modified',16,1)
Rollback
End
End
Ex-3:Define a trigger on dept so that it insert a row INTO the
dept details table also, where the dept id should be the current maxvalue+1 and
deptno should be the number being inserted INTO dept and the comments as null.
Sol:
CREATE TRIGGER dept_trg ON dept
AFTER INSERT
AS
Begin
DECLARE @did INT
DECLARE @ deptno INT
Select @did= max(did)+1 from deptdetails
Select @deptno=deptno from inserted
Insert INTO dept details (did,deptno) VALUES (@did,@deptno)
End
NESTED TRIGGERS:
A trigger invoking another trigger internally is called nested triggers. We can
nest upto 32 levels.
Ex-4:Write a trigger on emp so that it checks whether the given
deptno is available in the dept table or not and insert the row INTO the dept if
not present.
CREATE trigger emp_trgh on emp
AFTER INSERT
AS
BEGIN
DECLARE @deptno INT
Select @DEptno=deptno from inserted
If Not Exists(select * from dept where deptno=@deptno)
Begin
Insert INTO dept (deptno) VALUES
(@deptno)
End
End
We can control execution of nested triggers we can make a nested triggers to fire
not by using the following statements.
Sp_trigger 'Nested Triggers' (1) } [on/off] -off Reconfigure
If we define an instead of trigger of trigger on a complex view we can make the
complex view as updatable.By default a complex view is non updatable.
CREATE VIEW emp_dept_view
AS
Select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e
inner join dept d on e.deptno=d.deptno
Insert INTO emp_dept_view VALUES(1016,'cvn',3000,50,'rsearch','hyd')
The insert stmt will not execute becoz internally the VALUES has to be inserted
INTO emp as well as dept table also. If this stmt has to execute we should define 'instead of insert' trigger on the view.
CREATE TRIGGER emp_dept_view_trg on emp_dept_view
Instead of insert
AS
Begin
Insert INTO emp (empno,ename,sal,deptno) VALUES
(Select empno,ename,sal,deptno
from inserted)
Insert INTO dept (deptno,dname,loc) VALUES
(Select deptno,dname,loc
from inserted)
End
Insert INTO emp_dept_view VALUES(1016,'ram',2000,50,'research','hyd')
If you wanted to perform a delete operation on the composite view we erquired to
write a ‘instead of delete’ trigger and an update operation required ‘instead
of update’ trigger.
CREATE TRIGGER emp_dept_view_deletetrg on emp_dept_view
Instead of delete
AS
Begin
Delete from emp where empno=(select deptno from deleted)
End
When we perform an update on a view the instead of trigger should be used in the
VALUES from the deleted table in the case of the condition, and VALUES from the
inserted table in the case of set VALUES.
CREATE TRIGGER emp_dept_view_updtrg on emp_dept_view
Instead of Update
AS
Begin
DECLARE @empno INT
DECLARE @ename VARCHAR(50)
DECLARE @sal money
DECLARE @deptno INT
DECLARE@dname VARCHAR(50)
DECLARE@loc VARCHAR(50)
set @empno=empno,@ename=ename,@sal=sal,@deptno=deptno,@dname=dname,@loc=loc
from INSERTED
update emp set empno=@empno, ename=@ename,sal=@sal,deptno=@deptno
where empno=(select empno
from DELETED)
update dept set deptno=@deptno,dname=@dname ,loc=@loc
where deptno=(select deptno
from DELETED)
End