CREATE TABLE TestTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(20),
Pass NVARCHAR(20)
)
CREATE TABLE TR_Table (
ID INT IDENTITY(1,1) PRIMARY KEY,
DelID INT,
DelName NVARCHAR(20),
DelPass NVARCHAR(20),
Opration NVARCHAR(20),
OpDate DATETIME
)
CREATE TRIGGER MY_TEST_TRIGGER
ON TestTable
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @DelID INT;
DECLARE @DelName NVARCHAR(20);
DECLARE @DelPass NVARCHAR(20);
DECLARE @Operation NVARCHAR(20);
DECLARE @ACTION CHAR(1);
DECLARE @COUNT INT;
SET @ACTION = 'I';
SELECT @COUNT = COUNT(*) FROM DELETED
IF (@COUNT > 0)
BEGIN
SET @Action = 'D';
SELECT @COUNT = COUNT(*) FROM INSERTED
IF (@COUNT > 0)
BEGIN
SET @Action = 'U';
END
END
IF (@Action = 'D')
BEGIN
SET @Operation = 'DELETION'
SELECT @DelID = d.ID, @DelName = d.Name, @DelPass = d.pass FROM DELETED d
INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate)
VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
END
ELSE IF (@Action = 'I')
BEGIN
SET @Operation = 'INSERTION'
SELECT @DelID = i.ID, @DelName = i.Name, @DelPass = i.pass FROM INSERTED i
INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate)
VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
END
ELSE
BEGIN
SET @Operation = 'UPDATION'
SELECT @DelID = i.ID, @DelName = i.Name, @DelPass = i.pass FROM INSERTED i
INSERT INTO TR_Table (DelID,DelName,DelPass,Opration,OpDate)
VALUES (@DelID,@DelName,@DelPass,@Operation,GETDATE())
END
END
GO
INSERT INTO dbo.TestTable (NAME,PASS) VALUES ('Tejas','Tejas123')
UPDATE dbo.TestTable SET NAME = 'Tejas Vaishnav', PASS = 'Admin123' WHERE NAME = 'Tejas'
DELETE FROM dbo.TestTable WHERE NAME = 'Tejas Vaishnav'
SELECT * FROM TR_Table