Employee Table
Triggers:
Are the stored sub programs that are automatically invoked based on specified event?
A trigger are used to give conditions or rules on the tables so that meaningful data can be stored in the table. Constraints are having some drawbacks .To overcome the drawbacks triggers are used.
Sql server supports After Trigger, After Insert, After Update, and After Delete
Oracle supports before and after trigger, before insert, before delete, after insert, after update, after delete.
Trigger can’t be created without a table
Trigger wont accepts parameters
Triggers won’t returns a value.
Trigger called implicitly.
Difference between stored procedures, and trigger
Stored procedures, userdefinedfunctions | Triggers |
It must be invoke by the user Can take arguments | Trigger are automatically invoked based on specified event. Can’t take arguments |
Can be created without a table | Can’t be created without a table |
Stored procedures can return a value with output parameter and user defined function can return a value with return statement | Trigger cannot return a value either with output parameters or return statements. |
Based on the events specified for the triggers. Triggers are classified into
1 DDLTriggers
2 DML Triggers
DML triggers are the triggers that are created by specifying a DML command as event.
Dml triggers are new to sql server 2005
Dml triggers have the following three main purposes.
1 create procedural integrity constraints.
2 record auditing information of a table.
3 allow insert, update and delete on complex views.
To create a trigger use the create trigger command that has the following syntax to create DML Trigger.
Create trigger <Triggername> on <Tablename> |viewname
Sql server raises an error when an Instead of trigger is added to an Updatable view with check option. So first user must remove the option by using Alter View , before defining the Instead of Trigger
Error Message
select * from dept
Create trigger <Triggername> on <Tablename> |viewname
[with encryption]
For|after|instead of
[insert][,][update][,][delete]
As
Begin
Sqlstatements
End
By default after trigger
For|after|instead of
[insert][,][update][,][delete]
As
Begin
Sqlstatements
End
By default after trigger
After trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
This triggered are executed after the DML operation is executed.
Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, Sql Server will not fire the After Trigger.
create trigger trg12 on emp
after insert, update, delete
as
begin
if DATEPART(dw, getdate())=1
begin
rollback
raiserror('sunday not allowd',15,1)
end
end
INSERT INTO EMP VALUES (75213, 'WARD', 'SALESMAN', 7698,'02-22-1981', 1250, 500, 30);
Error Message as
sunday not allowd
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
delete from emp where EMPNO=7521
Error Message as
sunday not allowd
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
update emp set EMPNO=343 where empno=7521
Error Message as
sunday not allowd
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
After Insert Trigger
Example:
create table Students
(
studentid int identity,
studentname varchar(20),
address varchar(40)
)
insert Students values('uday','hyd')
insert Students values('raju','hyd')
insert Students values('anvesh','hyd')
Now, create another table to track studentdetailsaudit
create table Student_Demo_Audit
(
studentid int,
studentname varchar(50),
address varchar(50),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
create trigger trgAfterInsert1 on Students
for insert
as
begin
declare @studentid int,@studentname varchar(50), @address varchar(50),@audit_action varchar(100);
select @studentid=i.studentid from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'
insert into Student_Demo_Auditvalues (@studentid,@studentname,@address,@audit_action,getdate());
print'AFTER INSERT trigger fired.'
select * from Student_Demo_Audit
select * from Students
end
Now, insert the new record
insert Students values('adfas','hyd')
Now check the output
Select * from Student_Demo_Audit
6 NULL NULL Inserted Record -- After Insert Trigger. 2013-11-03 16:08:35.190
Select * from Students
1 uday hyd
2 raju hyd
3 anvesh hyd
4 adaf hyd
5 adaf hyd
6 adfas hyd
Trigger have inserted the new record to students_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.
After Update Trigger
create trigger trgAfterUpdate112 on Students
for update
as
begin
declare @studentid int,@studentname varchar(50), @address varchar(50),@audit_action varchar(100);
select @studentid=i.studentid from inserted i;
if(UPDATE(studentname))
set @audit_action='update Record -- After update Trigger.'
if(UPDATE(address))
set @audit_action='update Record -- After update Trigger.'
print'AFTER update trigger fired.'
insert into Student_Demo_Auditvalues (@studentid,@studentname,@address,@audit_action,getdate());
select * from Student_Demo_Audit
select * from Students
end
Output after updating the query
After delete Trigger
create trigger trgAfterDelete on Students
for delete
as
begin
declare @studentid int,@studentname varchar(50), @address varchar(50),@audit_action varchar(100);
select @studentid=i.studentid from deleted i;
set @audit_action='delete Record -- After delete Trigger.'
print'AFTER delete trigger fired.'
insert into Student_Demo_Auditvalues (@studentid,@studentname,@address,@audit_action,getdate());
select * from Student_Demo_Audit
select * from Students
end
delete Students Where studentid=1
Output:
Note: Specifying after is the same as specifying for.
After triggers can be specified only on tables.
Instead of trigger are not Allowed on Updatable view that use with check option,
For Table:
Delete option is not allowed on tables that have referential relationship specifying a cascade action onDelete.
Similarly the update option is not allowed on tables that have a referential relationship specifying a cascade action onupdate
Create a trigger that will restrict the operation to be performed before 9 am and after 5 pm.
create trigger emp_try1
on emp after insert,update,delete
as
begin
declare @dt int
set @dt=DATENAME(hh,getdate())
if @dt not between 9 and 15
begin
rollback
raiserror('cannot perform dml operations now', 15,1)
end
end
delete emp where EMPNO=7369
ErrorMessage
Msg 50000, Level 15, State 1, Procedure emp_try1, Line 10
cannot perform dml operations now
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Create a trigger on emp table for not allowing the user to perform insert, update, delete on Sunday and before 9 am and after 5 pm on regular days
create trigger dontallowdml on emp
for insert, update, delete
as
begin
declare @weeek int, @hour int
set @weeek=DATEPART(dw,getdate())
set @hour=DATEPART(hh,getdate())
if @weeek=1 or @hour<9 or @hour >16
begin
rollback transaction
print 'transacations are not allowed at this time'
end
endIf I write Rollback transaction without begin transaction than implicitly transaction will be created automatically.
When there is a trigger on the table than a transaction will be created implicitly regardless of whether or not implicitly transaction option is set to ON, and this transaction will be automatically completed immediately after executing a trigger.
Create a database trigger don’t allow any transaction before 10 am
create trigger try2 on emp
after insert, update,delete
as
begin
declare @hour int
set @hour=DATEPART(hh,getdate())
if (@hour<10)
begin
rollback
raiserror('invalid time',15,1)
end
end
Important Point
When we try to perform any DML operation on a table when a trigger is present on it the values of the DML statement will be captured in the trigger inside two Magic Tables
Inserted
Deleted
Magic Tables
Sql server provides two tables
1 Inserted and Deleted
That are accessible only within the trigger and the structure of these two tables will be same as structure of the table on which currently the trigger is executing.
Purpose of these two tables is to provide access to old and New Tables of the current row that is Inserted, Updated or Deleted from within the trigger.
Availability of a row in these Magic Tables related to Insert, Update, Delete are as follows:
Availability of a row in these Magic Tables related to Insert, Update, Delete are as follows:
Insert | Update | Delete | |
Inserted | Yes | Yes | No |
Deleted | No | Yes(old value | Yes(old value) |
Magic tables of inserted, deleted will contain only one row that is current row.
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
Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Instead of Insert Trigger
create TRIGGER trgInsteadOfInsertON students
INSTEAD OF Insert
AS
begin
declare @studentid int, @studentname varchar(55), @address varchar(50), @audit_action varchar(100);
select @studentid=i.studentid from inserted i;
select @studentname=i.studentname from inserted i;
select @address=i.address from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN
BEGIN TRAN
SET NOCOUNT ON
if(@studentname='abc')
begin
RAISERROR('Cannot Insert where studentname =abc',16,1);
ROLLBACK;
end
else
begin
Insert into students values(@studentname,@address)
Insert into Student_Demo_Auditvalues(@studentid,@studentname,@address,@audit_action,getdate());
COMMIT;
PRINT 'Record Inserted -- Instead Of Insert Trigger.'
end
end
end
output:
insert into students values('abc','hyd')
Errror Message
Msg 50000, Level 16, State 1, Procedure trgInsteadOfInsert, Line 15
Cannot Insert where studentid =1
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
insertinto students values('jack','hyd')
select* from students
select* from student_demo_audit
Instead of Update Trigger
createTRIGGER trgInsteadOfupdateON students
INSTEADOF update
AS
begin
declare@studentid int, @studentname varchar(55), @address varchar(50), @audit_action varchar(100);
select@studentid=i.studentid from inserted i;
select@studentname=i.studentname from inserted i;
select@address=i.address from inserted i;
SET@audit_action='Inserted Record -- Instead Of updateTrigger.';
BEGIN
BEGINTRAN
SETNOCOUNT ON
if(@studentname='abc')
begin
RAISERROR('Cannot update where studentname =abc',16,1);
ROLLBACK;
end
else
begin
updatestudents set studentname=@studentname where studentid=@studentid
Insertinto Student_Demo_Auditvalues(@studentid,@studentname,@address,@audit_action,getdate());
COMMIT;
PRINT'Record updated -- Instead Of update Trigger.'
end
end
end
updatestudents set studentname='abc' where studentid=1
Msg 50000, Level 16, State 1, Procedure trgInsteadOfupdate, Line 15
Cannot update where studentname =abc
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted
updatestudents set studentname='kittu' where studentid=1
Instead of Delete Trigger
createTRIGGER trgInsteadOfdeleteON students
INSTEADOF delete
AS
begin
declare@studentid int, @studentname varchar(55), @address varchar(50), @audit_action varchar(100);
select@studentid=i.studentid from inserted i;
select@studentname=i.studentname from inserted i;
select@address=i.address from inserted i;
SET@audit_action='deleted Record -- Instead Of delete Trigger.';
BEGIN
BEGINTRAN
SETNOCOUNT ON
if(@studentname='abc')
begin
RAISERROR('Cannot delete where studentname =abc',16,1);
ROLLBACK;
end
else
begin
deletestudents where studentname=@studentname
Insertinto Student_Demo_Auditvalues(@studentid,@studentname,@address,@audit_action,getdate());
COMMIT;
PRINT'Record deleted -- Instead Of delete Trigger.'
end
end
end
----------------------------------------------------------------------------------------------------------------------
Trigger Examples:
A trigger that will convert the dname and location into upper case when the user inserts in lower case
CREATETRIGGER DEPT_CONVERT_TRG
ONDEPT AFTER INSERT
AS
BEGIN
DECLARE@DEPTNO INT
DECLARE@DNAME VARCHAR(50)
DECLARE@LOC VARCHAR(50)
SELECT@DEPTNO=DEPTNO, @DNAME=DNAME, @LOC=LOC FROM INSERTED
UPDATEDEPT SET DNAME=UPPER(@DNAME), LOC=UPPER(@LOC) WHERE DEPTNO=@DEPTNO
END
Output:
insertinto dept values(60,'finance','hyd')
A trigger that will restrict to update the salary of the employee if the New Salary is less than the old salary.
CREATETRIGGER EMP_UPDATE_TRG
ONEMP AFTER UPDATE
AS
BEGIN
DECLARE@OLDSAL MONEY
DECLARE@NEWSAL MONEY
SELECT@OLDSAL=SAL FROM DELETED
SELECT@NEWSAL=SAL FROM INSERTED
IF@OLDSAL > @NEWSAL
BEGIN
ROLLBACK
RAISERROR('NEW SAL CANNOT BE LESS THAN OLD SAL', 15,1)
END
END
To Test
update emp set sal=600 where empno=7369
Error Message
Msg 50000, Level 15, State 1, Procedure EMP_UPDATE_TRG, Line 12
NEW SAL CANNOT BE LESS THAN OLD SAL
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Create a trigger to insert record into resign table when employee record is deleted.
createtrigger trg3
onemp
afterdelete
as
begin
declare@eno int ,@doj datetime
select@eno=empno ,@doj=hiredate from deleted
insertinto resgin values(@eno,@doj )
end
Output:
Delete from emp where empno=7369
Create a trigger which will not allow the user to delete the records from emp table.
createtrigger trg1 on emp
afterdelete
as
begin
print'deletion not allowed'
rollbacktransaction
end
output:
deletefrom emp where empno=7499
(1 row(s) affected)
deletion not allowed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
It will delete the records permantely and when rollback transaction is given the records will retrieve again.
Note: in, the given example trigger program will execute only when delete command is executed on emp table.
Create a trigger to display records from Inserted Magic Tables.
create trigger trg2
on emp
for insert
as
begin
select * from inserted
end
output:
INSERTINTO EMP VALUES (344, 'SMITH', 'CLERK', 7902,'12-17-1980', 800, NULL, 20);
Create a trigger to display records from deleted magic tables.
createtrigger trg34
onemp
fordelete
as
begin
select* from deleted
end
output:
delete from emp where EMPNO=7369
Create a trigger which will generate a unique deptno when a user insert a record into the dept. table only by specifying dname and loc when the primary key constraint is present on the deptno col.
createtrigger dept_Generate_Try
ondept instead of insert
as
begin
declare@deptno int
select@deptno=deptno from inserted
if@deptno is null
select@deptno=ISNULL(max(deptno),0)+10 from DEPT
insertinto dept select @deptno,dname,loc from inserted
end
output:
insertinto dept(dname,loc) values('research','hyd')
select* from dept
Create a data base trigger to not to allow more than four employee in a dept
create trigger trg1
on emp
instead of insert
as
begin
declare @eno int,@ename varchar(20)
declare @dno int, @cnt int
select @eno=empno, @ename=ename,@dno=deptno from inserted
select @cnt=count(*) from emp where deptno=@dno
if @cnt>4
raiserror('max 4 emp',15,1)
else
insert into emp(empno,ename,deptno) values(@eno,@ename,@dno)
end
insert into emp(empno,ename,deptno) values(1129,'dfsa',30)
a program which will restrict the delete operation if the job of the person is manager
create trigger checkmanager
on emp
after delete
as
begin
declare @job varchar(50)
select @job=job from deleted
if @job='Manager'
begin
rollback
raiserror('cannot delete manager from the table',15,1)
end
end
delete from emp where empno=7566
instead of Trigger
are the trigger that are executed instead of executing the given insert, update, and delete statement.
Main Purpose of Instead of Trigger is to allow insert, update, delete on complex view but instead of trigger can be executed on tables also.
Create a complex view that provides details of employee along with deptname and location in which the employee is working and than create an instead of trigger on it to allow insert
Create a complex view that provides access to details of employees along with deptname and location as follows
create view empdept as select E.*,dname,loc from EMP e inner join DEPT d on e.DEPTNO=d.DEPTNO
write the following insert statement on the view empdept which will cause error because of view is complex view
insert empdept values(1001,'a','clerk',7902,'10/21/990',2000,null,50,'computers','hyd')
create an instead of trigger on the view empdept as follows
create trigger allowinsert
on empdept instead of insert
as begin
declare @dno int
select @dno=deptno from inserted
if not exists(select * from dept wheredeptno=@dno)
begin
insert dept select deptno, dname,loc from inserted
end
insert emp select empno,ename,job,mgr,hiredate,sal,comm,deptno from inserted
end
insert empdept(empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc) values(10022,'a','clerk',7902,10/21/990,2000,null,50,'computers','hyd')
Create a trigger which will not allow the user to edit, add or delete from the table
create trigger trg4
on emp
for insert, update, delete
as
begin
print 'record can be viewed'
rollback transaction
end
Getting list of trigger
sp_helptrigger 'emp'
Disable and enable triggers
Alter table <Table name>
Disable or enable trigger <trigger name>
alter table emp
disable triggertrg1
To disable all trigger
alter table emp
disable triggerall
Getting definition of trigger
Sp_helptext ‘triggername’
Delete a trigge
Drop trigger <Triggername>
DDL Triggers
It is introduced from sql server 2005.
These trigger are created to control DDL operations like create, alter, drop
They can be used to perform administrative tasks in the database
Ddl triggers fire only after the ddl statement that trigger them.DDL Trigger cannot be used as instead of triggers,
DDL Trigger fires in 2 different cases likes
Database: applies the scope of DDL Trigger to the current database.
All server: applies the scope of a DDL or logon trigger to the current server.
Syntax:
Create trigger <name>
On db
After create, alter, drop
As
Begin
Statement
End
create trigger safety
on database
AFTER drop_Table,alter_Table
as
begin
raiserror('you cannot drop or modify any table',15,1)
rollback
end
create trigger safety
on ALLSERVER
AFTERCREATE_dATABASE
as
begin
PRINT 'DATABASE CREATED'
rollback
end
Dropping DDL Trigger
Syntax:
Drop trigger <Triggername> on db/allserver
Drop trigger safety on database
Drop trigger ddl_Trg on allserver
create trigger safety1
on database
AFTERcreate_table
as
begin
PRINT 'table CREATED'
rollback
end
How many instead of insert trigger can be created on a table
Answer: one
Nested Triggers:
Trigger can be nested to a maximum of 32 levels
If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger and so on.
To disable nested trigger, set the nested triggers option of sp_configure to zero (off)
