Scribd
Upload
32 views18 pages

Sqlserver Class22 (TSQL 5)

Uploaded by

hanuman sqlboy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
32 views18 pages

Sqlserver Class22 (TSQL 5)

Uploaded by

hanuman sqlboy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 18

TRIGGERS:

special kind of SP, which is executed automatically when some event occurs in
the database.

Events can be DML Evenets and DDL Events

INSERT / UPDATE / and DELETE are DML Events

Based on the events, Triggers are classified into two types


DML Triggers : which are associated with the DML events ( Insert / update /
delete ) - Table / view
DDL Triggers : which are associated with the DDL events ( create_table,
crete_procedure, alter_table, drop_table etc..)
Logon Triggers : wich are associated with the system events

DML Triggers
------------

CREATE TRIGGER <TrgName>


ON <objectName>
<triggertiming> <event>
AS
BEGIN

// Trigger code
END

Before : fires before the event (I/U/D)


after : fires after the event ( I/U/D)
instead of triggers : fires instead of the actual event

Note : you can have multiple AFTER DML triggers for the same event and on the same
object

Magic Tables
INSERTED and DELETED
these are accessed only with in the scope of triggers.

INSERTED : holds the new data information


DELETED : holds the old data

whenever we do Inserting the data , INSERETED will contain the new data, DELETED
will not contain anything
whenever we do delete , INSERETED will not contain the any data, DELETED table will
contain the old data
whenever we do update, INSERETED will contain the new data, DELETED table will
contain the old data

Exists Predicate returns a boolean value


it returns TRUE when the subquery has rows otherwise FALSE
-------- DB Script -----------

DROP TABLE Employee


DROP TABLE Department
CREATE TABLE Department
(
DeptId INT ,
DeptName VARCHAR(20)
);
GO

INSERT INTO Department VALUES (10,'IT');


INSERT INTO Department VALUES (20,'FINANCE');
INSERT INTO Department VALUES (30,'ADMIN');
INSERT INTO Department VALUES (40,'ACCOUNTS');

GO

--DROP TABLE Employee


CREATE TABLE Employee
(
EmpId int PRIMARY KEY,
EmpName VARCHAR(30),
EmpDesignation VARCHAR(20),
Salary int,
DOJ Datetime2 ,
DeptId INT
);

GO

INSERT INTO Employee VALUES (1,'Madhu','SE',1000,'12/15/2002',10);


INSERT INTO Employee VALUES (2,'Raju','SSE',1500,'10/21/2002',20);
INSERT INTO Employee VALUES (3,'Ramesh','SE',1200,'11/16/2002',20);
INSERT INTO Employee VALUES (4,'Sita','Trainee',800,'06/17/2003',10);
INSERT INTO Employee VALUES (5,'Rajesh','SE',1500,'01/19/2003',10);
INSERT INTO Employee VALUES (6,'Dinesh','SSE',2000,'01/08/2002',10);
GO

SELECT * FROM Department;


SELECT * FROM Employee;

-- Creating a AFTER INSERT trigger

SELECT * FROM Employee

CREATE TRIGGER Trg_Employee_Insert


ON Employee
AFTER INSERT
AS
BEGIN
PRINT 'Record Inserted into the Table'
END

INSERT INTO Employee VALUES (7,'Deepak','SSE',2000,'01/08/2003',10);


DELETE FROM Employee WHERE Empid = 7

ALTER TRIGGER Trg_Employee_Insert


ON Employee
AFTER INSERT
AS
BEGIN
SELECT * FROM INSERTED
SELECT * FROM DELETED
PRINT 'Record Inserted into the Table'
END

SELECT * FROM INSERTED


INSERT INTO Employee VALUES (7,'Deepak','SSE',2000,'01/08/2003',10);
DELETE FROM Employee WHERE Empid = 7

-- insertinting data into the different different table


CREATE TABLE Employee_Audit
(
EmpId int PRIMARY KEY,
EmpName VARCHAR(30),
EmpDesignation VARCHAR(20),
Salary int,
DOJ Datetime2 ,
DeptId INT ,
UserName VARCHAR(50),
insertedDate Datetime2
);

ALTER TRIGGER Trg_Employee_Insert


ON Employee
AFTER INSERT
AS
BEGIN
--SELECT * FROM INSERTED
--SELECT * FROM DELETED
--PRINT 'Record Inserted into the Table'
INSERT INTO Employee_audit
SELECT *,ORIGINAL_LOGIN(),Getdate() FROM INSERTED
END

DELETE FROM Employee WHERE Empid = 7

SELECT * FROM Employee


SELECT * FROM Employee_audit

INSERT INTO Employee VALUES (7,'Deepak','SSE',2000,'01/08/2003',10);


--Inserting multiple rows

SELECT * INTO Employee_SE


FROM Employee
WHERE EmpDesignation = 'SE'

select * from Employee_SE

ALTER TRIGGER Trg_Employee_Insert


ON Employee
AFTER INSERT
AS
BEGIN
SELECT * FROM INSERTED
SELECT * FROM DELETED
--PRINT 'Record Inserted into the Table'
--INSERT INTO Employee_audit
--SELECT *,ORIGINAL_LOGIN(),Getdate() FROM INSERTED
END

SELECT * FROM Employee_SE


UPDATE Employee_SE SET EmpId = 15 WHERE Empid = 5
INSERT INTO Employee

SELECT * FROM Employee


SELECT * FROM Employee_SE

INSERT INTO Employee


SELECT * FROM Employee_SE

DELETE FROM Employee where EmpId in (11,13,15)

ALTER TRIGGER Trg_Employee_Insert


ON Employee
AFTER INSERT
AS
BEGIN
--SELECT * FROM INSERTED
--SELECT * FROM DELETED
--PRINT 'Record Inserted into the Table'
INSERT INTO Employee_audit
SELECT *,ORIGINAL_LOGIN(),Getdate() FROM INSERTED
END

SELECT * FROM Employee


SELECT * FROM Employee_SE
SELECT * FROM Employee_audit

INSERT INTO Employee


SELECT * FROM Employee_SE

DELETE FROM Employee where EmpId in (7)

--
sp_helptrigger 'Employee'

DROP Trigger Trg_Employee_Insert

drop table employee


CREATE TABLE Employee
(
EmpId int PRIMARY KEY,
EmpName VARCHAR(30),
EmpDesignation VARCHAR(20),
);

GO

INSERT INTO Employee VALUES (1,'Madhu','SE');


INSERT INTO Employee VALUES (2,'Raju','SSE');
INSERT INTO Employee VALUES (3,'Ramesh','SE');
INSERT INTO Employee VALUES (4,'Sita','Trainee');
INSERT INTO Employee VALUES (5,'Rajesh','SE');
INSERT INTO Employee VALUES (6,'Dinesh','SSE');
GO

DROP TABLE Employee_Audit

CREATE TABLE Employee_Audit


(
EmpId int PRIMARY KEY,
old_EmpName VARCHAR(30),
New_EmpName VARCHAR(30),
New_EmpNamemodified datetime2,
Old_EmpDesignation VARCHAR(20),
New_EmpDesignation VARCHAR(20),
NewEmpDesignationModified datetime2,
UserName VARCHAR(50),
insertedDate Datetime2
);

6302069792

Qb27m1pIP

PNR- 4146807549

Return -- 4346690727
CREATE TRIGGER Trg_Employee_I_U_D
ON Employee
AFTER INSERT,UPDATE,DELETE
AS
BEGIN

IF (EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM


DELETED))
BEGIN
print ' some records are updated'
END
ELSE IF (EXISTS(SELECT 1 FROM INSERTED))
BEGIN
INSERT INTO
Employee_Audit(EmpId,New_EmpName,New_EmpDesignation,Username,insertedDat
e)
SELECT
EmpId,EmpName,EmpDesignation,ORIGINAL_LOGIN(),GETDATE() FROM
INSERTED
END
ELSE IF (EXISTS(SELECT 1 FROM DELETED))
BEGIN
print 'some records are deleted'
END

END
CREATE TRIGGER Trg_Employee_I
ON Employee
AFTER INSERT
AS
BEGIN
INSERT INTO
Employee_Audit(EmpId,New_EmpName,New_EmpDesignation,Username,insertedDat
e)
SELECT EmpId,EmpName,EmpDesignation,ORIGINAL_LOGIN(),GETDATE()
FROM INSERTED
END

select * from employee

INSERT INTO Employee VALUES(7,'Srikanth','PL')

select * from employee


select * from employee_audit

INSERT INTO Employee


VALUES
(8,'Srikanth2','PL'),
(9,'Srikanth3','PL')

INSERT INTO Employee


VALUES
(10,'Srikanth2','PL'),
(9,'Srikanth3','PL')
-- updating only single record
CREATE TRIGGER Trg_Employee_U
ON Employee
AFTER UPDATE
AS
BEGIN

IF (UPDATE(EmpId))
Begin
Rollback
RAISError('cannot update EmployeeId',16,1)
Return
End

-- INSERT INTO
Employee_Audit(EmpId,Old_EmpName,New_EmpName,Old_EmpDesignation,New_E
mpDesignation,Username,insertedDate)
--SELECT EmpId,EmpName,EmpDesignation,ORIGINAL_LOGIN(),GETDATE()
FROM INSERTED
END

select * from Employee


update Employee set empname = 'madhu sudhan' where empid = 1

select * from Employee_Audit

ALTER TRIGGER Trg_Employee_U


ON Employee
AFTER UPDATE
AS
BEGIN
DECLARE @oldEmpname AS VARCHAR(30)
DECLARE @OldEmpDesignation As VARCHAR(30)
DECLARE @newEmpname As VARCHAR(30)
DECLARE @NewEmpDesignation As VARCHAR(30)
DECLARE @EmpId AS INT

IF (UPDATE(EmpId))
Begin
Rollback
RAISError('cannot update EmployeeId',16,1)
Return
End

SELECT @EmpId = empid,@oldEmpname = Empname, @OldEmpDesignation


= EmpDesignation FROM DELETED
SELECT @newEmpname = Empname, @NewEmpDesignation =
EmpDesignation FROM INSERTED
IF (@oldEmpname <> @newEmpname)
UPDATE Employee_Audit SET Old_EmpName = @oldEmpname,
New_EmpName = @newEmpname WHERE Empid = @Empid

IF (@OldEmpDesignation <> @NewEmpDesignation)


UPDATE Employee_Audit SET Old_EmpDesignation =
@OldEmpDesignation, New_EmpDesignation = @NewEmpDesignation WHERE Empid
= @Empid

END

select * from Employee_Audit


Select * from employee
delete from employee

INSERT INTO Employee VALUES (1,'Madhu','SE');


INSERT INTO Employee VALUES (2,'Raju','SSE');
INSERT INTO Employee VALUES (3,'Ramesh','SE');
INSERT INTO Employee VALUES (4,'Sita','Trainee');
INSERT INTO Employee VALUES (5,'Rajesh','SE');
INSERT INTO Employee VALUES (6,'Dinesh','SSE');
select * from employee_audit
update employee set empid = 10 where empid = 1

UPDATE employee SET EmpName = 'Madhu Sudhan', Empdesignation = 'SSE' where


empid = 1
UPDATE employee SET EmpName = 'Madhu Sudhan Rao' where empid = 1
UPDATE employee SET Empdesignation = 'TL' where empid = 1
UPDATE employee SET EmpName = 'Madhu Sudhan Rao', Empdesignation = 'TL1'
where empid = 1

-- updating only Muliple records


ALTER TRIGGER Trg_Employee_U
ON Employee
AFTER UPDATE
AS
BEGIN
DECLARE @oldEmpname AS VARCHAR(30)
DECLARE @OldEmpDesignation As VARCHAR(30)
DECLARE @newEmpname As VARCHAR(30)
DECLARE @NewEmpDesignation As VARCHAR(30)
DECLARE @EmpId AS INT

IF (UPDATE(EmpId))
Begin
Rollback
RAISError('cannot update EmployeeId',16,1)
Return
End

SELECT * INTO #temp


FROM INSERTED
WHILE (EXISTS ( SELECT 1 FROM #Temp ))
Begin

SELECT TOP 1 @EmpId = empid,@oldEmpname = Empname,


@OldEmpDesignation = EmpDesignation FROM #Temp

SELECT @oldEmpname = Empname, @OldEmpDesignation =


EmpDesignation FROM DELETED WHERE Empid = @EmpId

SELECT @newEmpname = Empname, @NewEmpDesignation =


EmpDesignation FROM INSERTED WHERE Empid = @EmpId

IF (@oldEmpname <> @newEmpname)


UPDATE Employee_Audit SET Old_EmpName = @oldEmpname,
New_EmpName = @newEmpname WHERE Empid = @Empid

IF (@OldEmpDesignation <> @NewEmpDesignation)


UPDATE Employee_Audit SET Old_EmpDesignation =
@OldEmpDesignation, New_EmpDesignation = @NewEmpDesignation WHERE Empid
= @Empid

DELETE FROM #temp WHERE Empid = @empid


END

END

Select * from employee


select * from employee_audit
update employee set empid = 10 where empid = 1

UPDATE employee SET Empdesignation = 'SSE' where empdesignation = 'SE'


UPDATE employee SET EmpName = 'Madhu Sudhan Rao'
UPDATE employee SET Empdesignation = 'TL' where empid = 1

UPDATE employee SET EmpName = 'Madhu Sudhan Rao', Empdesignation = 'TL1'


where empid = 1

sp_helptrigger 'Employee'

You might also like