DML Triggers in SQL Server :-
- These triggers are created on particular table.
- These triggers are created to control DML operators.
Syntax :-
Create /alter trigger <name>
Or<tablename>/<viewname>
After/instead of insert,update,delete →triggers event
As
Begin
Statements
End.
After triggers in SQL Server :-
These triggers are fired (executed) after the DML operation is executed.
INSTEAD OF triggers in SQL Server :-
These triggers fired instead of DML operation.
Example :-
Create a database trigger don’t allow any DML operation on Sunday.
Create trigger trg1 on emp
After insert,update,delete
As
Begin
If datepart(dw,getdate ( ))=1
Begin
Rollback
Raiserror(‘Sunday not allowed’,15,1)
End
End.
↓
Insert into emp(empno ,ename,sal)values(8800,’’ABC’,4000)
↓
Select * from emp.
Example :-
So, create a database trigger don’t allow any transaction before 10 A.M?
Create trigger trg2 on emp
After insert,update,delete
As
Begin
If datepart(HH,getdate ( )<10)
Begin
Rollback
Raiserror(‘invalid time’,15,1)
End
End.
↓
Set implicit transaction on
Update emp set sal=sal+1000.
Note :- If table,trigger events are same instead of creating two different triggers write the code in one trigger.
Magic Tables in Triggers :-
Triggers using some tables are called as “magic table”.
Magic table
↓ ↓
Inserted Deleted
↓
Record affected by insert command is copied to inserted table.
Deleted :-
- Record affected by delete command is copied to deleted table.
- The record affected by update command is copied to both inserted table and deleted table.
- New record is copied to inserted table and old record is copied to deleted table.
4000 →Update emp set sal=5000 Where empno=719
Program :- Create a database trigger to insert record into resign table when employee resigns :-
Resign table :-
| empno | doj | Dor |
Create trigger trg3
On emp
After delete
As
Begin
Declare @eno,int,@doj datetime
Select @eno=empno,@doj=hiredate
From deleted
Insert into resign values(@eno,@doj,getdate ( ))
End.
↓
Delete from emp where empno=7369
↓
Select * from resign.
Example program :-
To create a trigger to convert deptname,locations to uppercase when user inserts record into department.
Create trigger trg4
On dept
Instead of insert
As
Begin
Declare @dno int,@dname varchar(20)
Declare @loc varchar (20)
Select @dno=deptno,@dname=dname,@loc =loc
From inserted
Insert into dept values(@dno,upper(@dname),upper(@loc))
↓
Insert into emp values(10,’hr’,’hyd’)
↓
Select * from emp
↓
10 HR HYD
Create a database trigger do not to allow more than four employee in a dept.
Create trigger trg1
On emp22
Instead of insert
As
Begin
Declare @eno int,@ename varchar (20)
Declare @dno int,@cnt int
Select @eno=empno ,@ename=ename,
@dno=dno from inserted
Select @cnt=count(*) from emp22
Where dno=@dno.
If @cnt=4
Raiserror(‘max 4 emps’,15,1)
Else
Insert into emp22 values (@eno,@ename,@dno)
End.
Example :-
Jobs
Job lowsal highsal
Clerk 1000 2000
Manager 2001 4000
Salesman 1500 3000
Program :-
Create trigger trg2
On emp →to check the salary range of employee
After insert
As
Begin
Declare @job varchar (20),@sal smallmoney
Declare @sal smallmoney,@hsal smallmoney
Select @job=job,@sal=sal from inserted
Select @sal=lsal,@hsal=hsal from jobs
Where job=@job
If @sal not between @lsal and @hsal
Begin
Rollback
Raiserror(‘sal out of range’,15,1)
End
End.
Auditing in SQL Server :-
Example :
Emp_audit
Uname operation dtime new_eno new_ename new_dno old_eno old_ename
Dbo insert —– 1 x 30 null null
Dbo delete —— null null null 0 x
Program :-
Create table emp_audit(uname varchar(20)),operation varchar(20),otime datetime,new_eno int,new_ename varchar(20),new_dno int,old_eno int,old_ename varchar(20),old_dno int)
- Create trigger trg3
On emp22
After insert,update,delete
As
Begin
Declare @cnt1 int,@cnt2 int
Declare @eno int ,@ename varchar (20)
Declare @dno int
Select @cnt1=count(*)from inserted
Select @cnt=count(*)from deleted.
Select @cnt2((@cnt1=0)and(@cnt2=0))
Begin
Select @eno=empno,@ename=ename,@dno=dno
From inserted.
Insert into emp_audit values (user_name ( ),insert,
Getdate ( ),@eno,@ename,@dno,null,null,null)
End
Else if((@cnt1=0)and(@cnt2=1))
Begin
Select @eno=empno,@ename=ename,@dno=dno
From deleted.
Insert into emp_audit values(user_name(),’delete’,getdate ( ),null,null,null,@eno,@ename,@dno)
End.
End
{else((@cnt1=1)and (@cnt2=1))
Begin
Select @eno=empno,@ename=ename,@dno=dno from up
Insert into emp_audit values(user_name ( ),’update’}
Updating complex views :–
Initial of trigger can be used to update complex views.
Create view v10
As
Select e.empno,e.ename,e.sal,d.deptno,d.dname
From emp e,dept d
Where e.deptno=d.deptno
Insert into v10 values(——-)
↓
Here complex views doesn’t allow DML operation.
Program :-
Create trigger trg5
On v10
Instead of insert
As
Begin
Declare @eno int,@ename varchar(20)
Declare @sal smallmoney,@dno int
Declare @dname varchar (20)
Select @eno=empno,@ename=ename,@sal=sal,
@dno=deptno,@dname=dname
From inserted.
Insert into dept values(@dno,@dname,null)
Insert into emp(empno,ename,sal,deptno)
Values(@eno,@ename,@sal,@dno)
End.