My delete trigger is not working

Transact-SQL
1

i created a table called Staff and another one called Retired in between those table i created a Delete Trigger from staff so that each time a delete a content from staff table, i want it to trigger Retired table .... But the issue is that it will only trigger the ID , the rest is empty please help me out and below is the code of my trigger!!!

CODE FOR MY STAFF TRIGGER....

USE [FMC] GO /****** Object: Trigger [dbo].[RetiredStaff] Script Date: 11/14/2017 11:36:54 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================ -- Template generated from Template Explorer using:

-- Create Trigger (New Menu).SQL

-- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

-- See additional Create Trigger templates for more

-- examples of different Trigger statements.

-- This block of comments will not be included in ALTER trigger [dbo].[RetiredStaff] On [dbo].[Staff] for delete as Begin declare @Title nvarchar,@Surname nvarchar(50),@Firstname nvarchar(50),@Middlename nvarchar(50),@Othername nvarchar(50), @Gender nvarchar(50),@MaidenName nvarchar(50),@DOB nvarchar,@DOFA nvarchar(50),@DOC nvarchar(50),@Origin nvarchar(50),@LG nvarchar(50), @GLOE nvarchar,@DOE nvarchar(50),@IppisNo nvarchar(50),@DOLA nvarchar(50),@Conhess_Conmess nvarchar(50),@Step nvarchar(50),@PD nvarchar(50), @Mobile nvarchar,@Email_Address nvarchar(50),@StaffNo nvarchar(50),@Gl nvarchar(50),@DeptID nvarchar(50), @StaffId int;

select @Title = a.Title from inserted a; select @StaffId = c.StaffId from inserted c; select @Surname = c.Surname from inserted c; select @Firstname = c.Firstname from inserted c; select @Middlename = d.Middlename from inserted d; select @Othername = l.Othername from inserted l; select @Gender = l.Gender from inserted l; select @MaidenName = c.CreatedDate from inserted c; select @DOB = c.CreatedBy from inserted c; select @DOFA = c.Title from inserted c; select @DOC = c.DOC from inserted c; select @Origin = c.Origin from inserted c; select @LG = d.GLOE from inserted d; select @GLOE = l.GLOE from inserted l; select @DOE = c.DOE from inserted c; select @IppisNo = c.IppisNo from inserted c; select @DOLA = c.DOLA from inserted c; select @Conhess_Conmess = c.Conhess_Conmess from inserted c; select @Step = c.Step from inserted c; select @PD = d.PD from inserted d; select @Mobile = l.Mobile from inserted l; select @Email_Address = c.Email_Address from inserted c; select @StaffNo = c.StaffNo from inserted c; select @Gl = c.Gl from inserted c; select @DeptID = c.DeptID from inserted c;

insert into Retiree (Title ,Surname ,Firstname ,Middlename ,Othername ,Gender ,MaidenName ,DOB ,DOFA ,DOC ,Origin ,LG ,GLOE ,DOE ,IppisNo ,DOLA ,Conhess_Conmess ,Step ,PD ,Mobile ,Email_Address ,StaffNo ,Gl ,DeptID,StaffId) VALUES (@Title,@Surname,@Firstname,@Middlename,@Othername,@Gender,@MaidenName,@DOB,@DOFA, @DOC,@Origin,@LG,@GLOE,@DOE,@IppisNo,@DOLA,@Conhess_Conmess,@Step,@PD,@Mobile,@Email_Address,@StaffNo,@Gl,@DeptID,@StaffId)

End

2

This only works for a single row. You need to write your triggers so that they work for multiple rows in INSERTED / DELETED table(s)

Something like this:

INSERT INTO Retiree
(
    Title, Surname, Firstname, ...
)
SELECT Title, Surname, Firstname, ...
FROM DELETED AS D

if the row might already exist in Retiree then append:

WHERE NOT EXISTS (SELECT * FROM Retiree AS R WHERE R.StaffId = D.StaffId)

(assuming that StaffId column is a unique identifier for both tables)

1 Like