A trigger is an operation that is executed when some kind of event occurs to the database.
You can't use Trigger With
cannot create or modify Database objects using triggers
For example, cannot perform “CREATE TABLE… or ALTER TABLE” sql statements under the triggers
cannot perform any administrative tasks
For example, cannot perform “BACKUP DATABASE…” task under the triggers
cannot pass any kind of parameters
cannot directly call triggers
WRITETEXT statements do not allow a trigger
Use Pubs
-- Copy all the values into the Temptitles
SELECT * into temptitles
FROM titles
-- Demo to Display the 2 Special(Magic) TABLEs.
CREATE TRIGGER trgup_TempTitles on temptitles
FOR UPDATE
AS
BEGIN
PRINT 'Displaying Deleted TABLE'
PRINT '-------------------------------------'
PRINT '-------------------------------------'
SELECT pub_id FROM deleted
PRINT '-------------------------------------'
PRINT '-------------------------------------'
PRINT 'Displaying Inserted TABLE'
PRINT '-------------------------------------'
PRINT '-------------------------------------'
SELECT pub_id FROM inserted
ROLLBACK TRAN
END
Note
ROLLBACK TRANSACTION erases all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
--Execute the following statement to check FOR magic TABLEs when the TRIGGER fires
UPDATE temptitles
SET pub_id = 0736
WHERE pub_id = 1389
Create database ITShareDB
-- Use InfyENR
use ITShareDB
GO
/* Insert TRIGGER */
-- CREATE a TABLE with the following description
-- Employee code
-- Employee Name
-- Employee Age
-- Employee department code
-- Emploayee Salary
CREATE TABLE Emp
(
cECode char(4) Constraint PK_Emp Primary Key,
vEName Varchar(25) not null,
tiEAge tinyint,
cEDCode char(4) Constraint FK_PCODE References Department(cDCode),
fESal float
)
--CREATE the Department TABLE with following description
-- Department Code
-- Department Name
-- Strength (Maximum no of employees FOR a Department
-- Filled (Number of Employees who are currently in the department
CREATE TABLE Department
(
cDCode char(4) constraint PK_Position Primary Key,
vDDesc varchar(15) not null,
iDStrength int,
iDFilled int
)
-- CREATE the Recruitment TABLE with following description ( Recruitment drive)
-- Recruitment Code
-- Recruitment Dept ( Recruitment drive FOR the Department )
-- Recruitment Advertisement Number
-- Number of Post ( Number to be filled FOR a Department)
CREATE TABLE Recruitment
(
cRecCode char(4) constraint PK_Rec Primary Key,
cDCode char(4) constraint FK_RecDCode FOReign key references Department(cDCode),
cRecAdv Char(4),
iRecNoPost int
)
-- Insert values into the Department TABLE
insert into Department values('D001','EISA',100,70)
insert into Department values('D002','BCMD',150,130)
insert into Department values('D003','ILI',200,160)
insert into Department values('D004','ENR',500,380)
insert into Department values('D005','HRD',800,670)
insert into Department values('D006','IVS',1000,830)
GO
--Insert values into the Emp TABLE
insert into emp values('E001','Shyam',24,'D003',25000)
insert into emp values('E002','Sundar',25,'D002',65000)
insert into emp values('E003','Raj',24,'D004',22000)
insert into emp values('E004','Ganesh',20,'D003',25000)
insert into emp values('E005','Deepak',21,'D004',23000)
GO
--Insert values into the Recruitment TABLE
insert into recruitment values('R001','D001','A001',10)
insert into recruitment values('R002','D003','A002',8)
insert into recruitment values('R003','D005','A003',10)
GO
/* CREATE a TRIGGER FOR insert on recruitment TABLE such that
the recruitment advertisement placed should not be inserted if NoOfPost is greater than
the available vacancy in the Department (ie the NoOfPost should not be greater than the
difference between the iStremgth and Ifilled of Department TABLE */
CREATE TRIGGER trg_Ins_Rec on Recruitment
FOR Insert
AS
BEGIN
Declare @NoOfPost int
Declare @Vacancy int
-- Store the Adv Post in a Variable
SELECT @NoOfPost = iRecNoPost FROM inserted
-- Store the Vacancy in a Variable
SELECT @Vacancy = (iDStrength - iDFilled)
FROM Department join Inserted on
Department.cDCode = Inserted.cDCode
--Check whether NoOfPost is Greater than Vacancy
if @NoOfPost > @Vacancy
BEGIN
Raiserror('No of Post Cannot be Greater than Vacancy',10,1)
ROLLBACK TRAN
END
else
BEGIN
PRINT 'TRANsaction COMMITted '
COMMIT TRAN
END
END
-- try this out
SELECT * FROM department
SELECT * FROM recruitment
-- Insert a Value WHERE NoOfPost is less than Vacancy
insert into recruitment values('R004','D001','A004',10)
-- Insert a Value WHERE NoofPost is Greater than Vacancy
insert into recruitment values('R005','D001','A005',50)
/* CREATE a TRIGGER on Emp TABLE FOR insert */
/* When a New Employee is Inserted the Value of iDFilled in the Department TABLE
should be Incremented by 1 FOR the corresponding Dept code at the same time check
FOR the value so that the filled value is not greater than the strength value */
CREATE TRIGGER trg_Ins_Emp on Emp
FOR insert
AS
BEGIN
Declare @vacancy int
-- Get the difference between iDStrength and iDFilled
SELECT @vacancy = (iDStrength - iDFilled)
FROM department join Inserted on
department.cDCode = Inserted.cEDCode
If @vacancy > 0
BEGIN
UPDATE Department
SET iDFilled = iDFilled + 1
FROM Department join Inserted on
Department.cDCode = Inserted.cEDCode
PRINT 'Post in Department Incremented'
END
else
BEGIN
raiserror('No Vacancy in the Department ',10,1)
ROLLBACK TRAN
END
END
--Try this out
SELECT * FROM department
SELECT * FROM emp
insert into emp values('E006','RaghavENDra',29,'D004',23000)
-- Insert a New row into department with strength and filled AS 100 and 100
insert into Department values('D007','Fac',100,100)
insert into emp values('E007','Venkatesh',29,'D007',20000)
/* CREATE a Procedure to be called in a TRIGGER */
SELECT * FROM sysobjects WHERE type='p'
CREATE proc prcTest(@DCode char(4),@Vacancy int output)
AS
BEGIN
SELECT @Vacancy = (iDStrength - iDFilled) FROM Department
WHERE cDCode = @DCode
END
SELECT * FROM Department
Declare @fill int
Exec prcTest 'D002',@fill output
SELECT @fill
/* CREATE a TRIGGER with procedure call inside */
Drop TRIGGER trg_ins_rec
CREATE TRIGGER trg_Ins_Rec_With_Proc on Recruitment
FOR Insert
AS
BEGIN
Declare @NoOfPost int
Declare @Vacancy int
declare @dcode char(4)
-- Store the Adv Post in a Variable
SELECT @NoOfPost = iRecNoPost FROM inserted
-- Store the Vacancy in a Variable
SELECT @dcode = cdcode FROM inserted
Exec prcTest @dcode,@Vacancy output
--Check whether NoOfPost is Greater than Vacancy
if @NoOfPost > @Vacancy
BEGIN
Raiserror('No of Post Cannot be Greater than Vacancy',10,1)
ROLLBACK TRAN
END
else
BEGIN
PRINT 'TRANsaction COMMITted '
COMMIT TRAN
END
END
--Try this out
SELECT * FROM recruitment
insert into recruitment values('R005','D001','A005',50)
/* CREATE a Function to be called within a TRIGGER */
CREATE Function fn_Dep_Vacancy(@DCode char(4))
Returns int
AS
BEGIN
Declare @Vacancy int
SELECT @Vacancy = (iDStrength - iDFilled) FROM Department
WHERE cDCode = @DCode
Return @Vacancy
END
/* Write a TRIGGER to call the function */
drop TRIGGER trg_Ins_Rec_With_Proc
CREATE TRIGGER trg_Ins_Rec_With_Func on Recruitment
FOR Insert
AS
BEGIN
Declare @NoOfPost int
Declare @Vacancy int
declare @dcode char(4)
-- Store the Adv Post in a Variable
SELECT @NoOfPost = iRecNoPost FROM inserted
-- Store the Vacancy in a Variable
SELECT @dcode = cdcode FROM inserted
Exec @Vacancy = fn_Dep_Vacancy @dcode
--Check whether NoOfPost is Greater than Vacancy
if @NoOfPost > @Vacancy
BEGIN
Raiserror('No of Post Cannot be Greater than Vacancy',10,1)
ROLLBACK TRAN
END
else
BEGIN
PRINT 'TRANsaction COMMITted '
COMMIT TRAN
END
END
--Try this out
SELECT * FROM recruitment
insert into recruitment values('R005','D001','A005',50)
/* Delete TRIGGERs */
/* Write a TRIGGER that gives a Message 'Not Authorised to Delete'
and ROLLBACK the TRANsaction*/
CREATE TRIGGER Trg_Del_Emp_Not_Auth on Emp
FOR Delete
AS
BEGIN
raiserror('Not Authorised to Delete',10,1)
ROLLBACK TRAN
END
-- Try this out
DELETE Emp
/* CREATE a TRIGGER on Emp FOR delete when a employee Gets deleted the iDFiiled in department
must decrement by one */
Drop TRIGGER trg_del_emp
CREATE TRIGGER Trg_Del_Emp on Emp
FOR delete
AS
BEGIN
UPDATE Department
SET iDFilled = iDFilled - 1
FROM department join deleted on
department.CDCode = deleted.cEDCode
END
--Try this out
SELECT * FROM Emp
SELECT * FROM Department
Delete emp
WHERE cecode = 'E006'
/* UPDATE TRIGGERs */
-- CREATE a TRIGGER on Recruitment FOR UPDATE such the value enter FOR iRecNoPost
-- should not be negative
CREATE TRIGGER trg_Upd_Recruitment on Recruitment
FOR UPDATE
AS
BEGIN
if ((SELECT iRecNoPost FROM Inserted) < 0)
BEGIN
PRINT 'Value cannot be Negative'
ROLLBACK TRAN
END
END
SELECT * FROM department
SELECT * FROM recruitment
--Try this out
UPDATE recruitment
SET irecnopost = irecnopost - 10
WHERE cRecCode = 'R001'
/* To Check FOR a UPDATE on a PArticular Column of the TABLE */
-- TRIGGER can be fired only if UPDATE is done on a particular column of a TABLE
/* Write a TRIGGER on emp FOR UPDATE such that when a UPDATE is done on the
deptcode on the emp TABLE the iDFilled should decrement by one FOR existing deptcode
and iDFilled should increment FOR new ASsigned deptcode */
CREATE TRIGGER trg_Upd_Emp_Dept on Emp
FOR UPDATE
AS
BEGIN
if UPDATE(cEDCode)
BEGIN
UPDATE department
SET iDFilled = iDFilled + 1
FROM department join inserted on
department.cDCode = Inserted.cEDCode
UPDATE department
SET iDFilled = iDFilled - 1
FROM department join deleted on
department.cDCode = deleted.cEDCode
END
END
--Try this out
SELECT * FROM Emp
SELECT * FROM Department
UPDATE emp
SET cEDCode = 'D002'
WHERE cECode = 'E002'
-- Try on other column UPDATE
UPDATE emp
SET fESal = 45000
WHERE cECode = 'E002'
/* Instead Of TRIGGER */
Drop view vwEmp
--Create a view vwEmp with all columns except Primary key column cECode
CREATE view vwEmp
AS
SELECT vEName,tiEAge,cEDCode,fESal
FROM Emp
SELECT * FROM emp
SELECT * FROM vwEmp
-- Try inserting values through view
insert into vwEmp values('NagENDra',21,'D002',26000)
/* CREATE a Function that will return a New Emp Code */
CREATE function fn_Ret_New_Ecode()
returns char(4)
AS
BEGIN
Declare @ECode char(4)
Declare @NewCode int
SELECT @NewCode = convert(int,max(substring(cecode,2,3))) FROM emp
SET @NewCode = @NewCode + 1
If @newcode < 10
SET @ECode = 'E00' + convert(char(1),@newcode)
else If @newcode < 100
SET @ECode = 'E0' + convert(char(2),@newcode)
else If @newcode < 1000
SET @ECode = 'E' + convert(char(3),@newcode)
return @ECode
END
/* Test the Function */
Declare @n char(4)
Exec @n = fn_Ret_New_Ecode
SELECT @n
/* Write the Instead of TRIGGER */
CREATE TRIGGER trg_InsOf_emp on vwEmp
instead of Insert
AS
BEGIN
Declare @cECode char(4)
Declare @vEName Varchar(25)
Declare @tiEAge tinyint
Declare @cEDCode char(4)
Declare @fESal float
Exec @cECode = fn_Ret_New_Ecode
SELECT @vENAme = vEName , @tiEAge = tiEAge ,
@cEDCode = cEDCode , @fESal =fESal
FROM Inserted
Insert into Emp values
(@cECode,@vENAme, @tiEAge, @cEDCode, @fESal)
END
-- Now try this
SELECT * FROM emp
SELECT * FROM vwEmp
-- Now try this
Insert into vwEmp values('Nagendra',21,'D002',26000)
/* Instead Of TRIGGER can also be defined on a TABLE */
--Delete on mASter should not be allowed
use pubs
CREATE TRIGGER Trg_InsOf_Authors on Authors
Instead Of Delete
AS
BEGIN
PRINT ' Cannot Delete records FROM MASters'
ROLLBACK TRAN
END
--Try this out
delete authors
SELECT * FROM Authors
/* Write the Instead of TRIGGER */
CREATE TRIGGER trg_InsOf_emp on vwEmp
instead of Insert
AS
BEGIN
Declare @vEName Varchar(25)
Declare @tiEAge tinyint
Declare @cEDCode char(4)
Declare @fESal float
SELECT @vENAme = vEName , @tiEAge = tiEAge ,
@cEDCode = cEDCode , @fESal =fESal
FROM Inserted
Insert into Emp values
('E006',@vENAme, @tiEAge, @cEDCode, @fESal)
END
drop trigger Trg_Del_Emp_Not_Auth
delete emp where cecode ='E006'
