My delete trigger is not working finr

Question

My delete trigger is not working finr

SSC Enthusiast

Points: 126

November 14, 2017 at 6:03 am

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76987 More actions · Answer 1

First, no one is going to read that mess. Use the "SQL Code" tag at the bottom of the posting box to include formatted SQL code.

Second, GET RID OF THE VARIABLES. Your trigger is coded so that it will fail if more than one row is ever deleted. You need to stop thinking in procedural terms and start thinking in set-based terms.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, here is your code well formatted (well, sort of):
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 [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

Second, you don't need separate select statements for each variable:
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 [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             = [c].[Title]
, @StaffId         = [c].[StaffId]
, @Surname         = [c].[Surname]
, @Firstname       = [c].[Firstname]
, @Middlename      = [c].[Middlename]
, @Othername       = [c].[Othername]
, @Gender          = [c].[Gender]
, @MaidenName      = [c].[CreatedDate]
, @DOB             = [c].[CreatedBy]
, @DOFA            = [c].[Title]
, @DOC             = [c].[DOC]
, @Origin          = [c].[Origin]
, @lg              = [c].[GLOE]
, @GLOE            = [c].[GLOE]
, @DOE             = [c].[DOE]
, @IppisNo         = [c].[IppisNo]
, @DOLA            = [c].[DOLA]
, @Conhess_Conmess = [c].[Conhess_Conmess]
, @Step            = [c].[Step]
, @PD              = [c].[PD]
, @mobile          = [c].[Mobile]
, @Email_Address   = [c].[Email_Address]
, @StaffNo         = [c].[StaffNo]
, @Gl              = [c].[Gl]
, @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
GO

But as you were told above, this trigger will only work for one row deletes. It won't work as you expect if you were to delete multiple rows of data at a time. This is why you need to think set-based not row-based:
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 [dbo].[RetiredStaff] On [dbo].[Staff]
for delete
as
BEGIN

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)
SELECT
[c].[Title]
, [c].[StaffId]
, [c].[Surname]
, [c].[Firstname]
, [c].[Middlename]
, [c].[Othername]
, [c].[Gender]
, [c].[CreatedDate]
, [c].[CreatedBy]
, [c].[Title]
, [c].[DOC]
, [c].[Origin]
, [c].[GLOE]
, [c].[GLOE]
, [c].[DOE]
, [c].[IppisNo]
, [c].[DOLA]
, [c].[Conhess_Conmess]
, [c].[Step]
, [c].[PD]
, [c].[Mobile]
, [c].[Email_Address]
, [c].[StaffNo]
, [c].[Gl]
, [c].[DeptID]
FROM
[inserted] [c];

END

PLEASE, ask questions about what is going on here.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis - Tuesday, November 14, 2017 9:05 AM
First, here is your code well formatted (well, sort of):
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 [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
Second, you don't need separate select statements for each variable:
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 [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             = [c].[Title]
, @StaffId         = [c].[StaffId]
, @Surname         = [c].[Surname]
, @Firstname       = [c].[Firstname]
, @Middlename      = [c].[Middlename]
, @Othername       = [c].[Othername]
, @Gender          = [c].[Gender]
, @MaidenName      = [c].[CreatedDate]
, @DOB             = [c].[CreatedBy]
, @DOFA            = [c].[Title]
, @DOC             = [c].[DOC]
, @Origin          = [c].[Origin]
, @lg              = [c].[GLOE]
, @GLOE            = [c].[GLOE]
, @DOE             = [c].[DOE]
, @IppisNo         = [c].[IppisNo]
, @DOLA            = [c].[DOLA]
, @Conhess_Conmess = [c].[Conhess_Conmess]
, @Step            = [c].[Step]
, @PD              = [c].[PD]
, @mobile          = [c].[Mobile]
, @Email_Address   = [c].[Email_Address]
, @StaffNo         = [c].[StaffNo]
, @Gl              = [c].[Gl]
, @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
GO
But as you were told above, this trigger will only work for one row deletes. It won't work as you expect if you were to delete multiple rows of data at a time. This is why you need to think set-based not row-based:
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 [dbo].[RetiredStaff] On [dbo].[Staff]
for delete
as
BEGIN
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)
SELECT
[c].[Title]
, [c].[StaffId]
, [c].[Surname]
, [c].[Firstname]
, [c].[Middlename]
, [c].[Othername]
, [c].[Gender]
, [c].[CreatedDate]
, [c].[CreatedBy]
, [c].[Title]
, [c].[DOC]
, [c].[Origin]
, [c].[GLOE]
, [c].[GLOE]
, [c].[DOE]
, [c].[IppisNo]
, [c].[DOLA]
, [c].[Conhess_Conmess]
, [c].[Step]
, [c].[PD]
, [c].[Mobile]
, [c].[Email_Address]
, [c].[StaffNo]
, [c].[Gl]
, [c].[DeptID]
FROM
[inserted] [c];
END
PLEASE, ask questions about what is going on here.

Damn, missed something BIG! All references to inserted need to be changed to deleted. I was in a hurry to get somewhere important this morning.

Starting with the last version of the 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 [dbo].[RetiredStaff] On [dbo].[Staff]
for delete
as
BEGIN

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)
SELECT
[c].[Title]
, [c].[StaffId]
, [c].[Surname]
, [c].[Firstname]
, [c].[Middlename]
, [c].[Othername]
, [c].[Gender]
, [c].[CreatedDate]
, [c].[CreatedBy]
, [c].[Title]
, [c].[DOC]
, [c].[Origin]
, [c].[GLOE]
, [c].[GLOE]
, [c].[DOE]
, [c].[IppisNo]
, [c].[DOLA]
, [c].[Conhess_Conmess]
, [c].[Step]
, [c].[PD]
, [c].[Mobile]
, [c].[Email_Address]
, [c].[StaffNo]
, [c].[Gl]
, [c].[DeptID]
FROM
[deleted] [c];

END

Then your version:
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 [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 [deleted] [a];
SELECT @StaffId = [c].[StaffId] FROM [deleted] [c];
SELECT @Surname = [c].[Surname] FROM [deleted] [c];
SELECT @Firstname = [c].[Firstname] FROM [deleted] [c];
SELECT @Middlename = [d].[Middlename] FROM [deleted] [d];
SELECT @Othername = [l].[Othername] FROM [deleted] [l];
SELECT @Gender = [l].[Gender] FROM [deleted] [l];
SELECT @MaidenName = [c].[CreatedDate] FROM [deleted] [c];
SELECT @DOB = [c].[CreatedBy] FROM [deleted] [c];
SELECT @DOFA = [c].[Title] FROM [deleted] [c];
SELECT @DOC = [c].[DOC] FROM [deleted] [c];
SELECT @Origin = [c].[Origin] FROM [deleted] [c];
SELECT @lg = [d].[GLOE] FROM [deleted] [d];
SELECT @GLOE = [l].[GLOE] FROM [deleted] [l];
SELECT @DOE = [c].[DOE] FROM [deleted] [c];
SELECT @IppisNo = [c].[IppisNo] FROM [deleted] [c];
SELECT @DOLA = [c].[DOLA] FROM [deleted] [c];
SELECT @Conhess_Conmess = [c].[Conhess_Conmess] FROM [deleted] [c];
SELECT @Step = [c].[Step] FROM [deleted] [c];
SELECT @PD = [d].[PD] FROM [deleted] [d];
SELECT @mobile = [l].[Mobile] FROM [deleted] [l];
SELECT @Email_Address = [c].[Email_Address] FROM [deleted] [c];
SELECT @StaffNo = [c].[StaffNo] FROM [deleted] [c];
SELECT @Gl = [c].[Gl] FROM [deleted] [c];
SELECT @DeptID = [c].[DeptID] FROM [deleted] [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

Then your version with a single SELECT:
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 [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             = [c].[Title]
, @StaffId         = [c].[StaffId]
, @Surname         = [c].[Surname]
, @Firstname       = [c].[Firstname]
, @Middlename      = [c].[Middlename]
, @Othername       = [c].[Othername]
, @Gender          = [c].[Gender]
, @MaidenName      = [c].[CreatedDate]
, @DOB             = [c].[CreatedBy]
, @DOFA            = [c].[Title]
, @DOC             = [c].[DOC]
, @Origin          = [c].[Origin]
, @lg              = [c].[GLOE]
, @GLOE            = [c].[GLOE]
, @DOE             = [c].[DOE]
, @IppisNo         = [c].[IppisNo]
, @DOLA            = [c].[DOLA]
, @Conhess_Conmess = [c].[Conhess_Conmess]
, @Step            = [c].[Step]
, @PD              = [c].[PD]
, @mobile          = [c].[Mobile]
, @Email_Address   = [c].[Email_Address]
, @StaffNo         = [c].[StaffNo]
, @Gl              = [c].[Gl]
, @DeptID          = [c].[DeptID]
FROM
[deleted] [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
GO

mrplayerplanet SSC Enthusiast Points: 126 More actions · Answer 4

Lynn Pettis - Tuesday, November 14, 2017 11:46 AM
Lynn Pettis - Tuesday, November 14, 2017 9:05 AM
First, here is your code well formatted (well, sort of):
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 [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
Second, you don't need separate select statements for each variable:
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 [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             = [c].[Title]
, @StaffId         = [c].[StaffId]
, @Surname         = [c].[Surname]
, @Firstname       = [c].[Firstname]
, @Middlename      = [c].[Middlename]
, @Othername       = [c].[Othername]
, @Gender          = [c].[Gender]
, @MaidenName      = [c].[CreatedDate]
, @DOB             = [c].[CreatedBy]
, @DOFA            = [c].[Title]
, @DOC             = [c].[DOC]
, @Origin          = [c].[Origin]
, @lg              = [c].[GLOE]
, @GLOE            = [c].[GLOE]
, @DOE             = [c].[DOE]
, @IppisNo         = [c].[IppisNo]
, @DOLA            = [c].[DOLA]
, @Conhess_Conmess = [c].[Conhess_Conmess]
, @Step            = [c].[Step]
, @PD              = [c].[PD]
, @mobile          = [c].[Mobile]
, @Email_Address   = [c].[Email_Address]
, @StaffNo         = [c].[StaffNo]
, @Gl              = [c].[Gl]
, @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
GO
But as you were told above, this trigger will only work for one row deletes. It won't work as you expect if you were to delete multiple rows of data at a time. This is why you need to think set-based not row-based:
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 [dbo].[RetiredStaff] On [dbo].[Staff]
for delete
as
BEGIN
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)
SELECT
[c].[Title]
, [c].[StaffId]
, [c].[Surname]
, [c].[Firstname]
, [c].[Middlename]
, [c].[Othername]
, [c].[Gender]
, [c].[CreatedDate]
, [c].[CreatedBy]
, [c].[Title]
, [c].[DOC]
, [c].[Origin]
, [c].[GLOE]
, [c].[GLOE]
, [c].[DOE]
, [c].[IppisNo]
, [c].[DOLA]
, [c].[Conhess_Conmess]
, [c].[Step]
, [c].[PD]
, [c].[Mobile]
, [c].[Email_Address]
, [c].[StaffNo]
, [c].[Gl]
, [c].[DeptID]
FROM
[inserted] [c];
END
PLEASE, ask questions about what is going on here.
Damn, missed something BIG! All references to inserted need to be changed to deleted. I was in a hurry to get somewhere important this morning.
Starting with the last version of the 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 [dbo].[RetiredStaff] On [dbo].[Staff]
for delete
as
BEGIN
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)
SELECT
[c].[Title]
, [c].[StaffId]
, [c].[Surname]
, [c].[Firstname]
, [c].[Middlename]
, [c].[Othername]
, [c].[Gender]
, [c].[CreatedDate]
, [c].[CreatedBy]
, [c].[Title]
, [c].[DOC]
, [c].[Origin]
, [c].[GLOE]
, [c].[GLOE]
, [c].[DOE]
, [c].[IppisNo]
, [c].[DOLA]
, [c].[Conhess_Conmess]
, [c].[Step]
, [c].[PD]
, [c].[Mobile]
, [c].[Email_Address]
, [c].[StaffNo]
, [c].[Gl]
, [c].[DeptID]
FROM
[deleted] [c];
END
Then your version:
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 [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 [deleted] [a];
SELECT @StaffId = [c].[StaffId] FROM [deleted] [c];
SELECT @Surname = [c].[Surname] FROM [deleted] [c];
SELECT @Firstname = [c].[Firstname] FROM [deleted] [c];
SELECT @Middlename = [d].[Middlename] FROM [deleted] [d];
SELECT @Othername = [l].[Othername] FROM [deleted] [l];
SELECT @Gender = [l].[Gender] FROM [deleted] [l];
SELECT @MaidenName = [c].[CreatedDate] FROM [deleted] [c];
SELECT @DOB = [c].[CreatedBy] FROM [deleted] [c];
SELECT @DOFA = [c].[Title] FROM [deleted] [c];
SELECT @DOC = [c].[DOC] FROM [deleted] [c];
SELECT @Origin = [c].[Origin] FROM [deleted] [c];
SELECT @lg = [d].[GLOE] FROM [deleted] [d];
SELECT @GLOE = [l].[GLOE] FROM [deleted] [l];
SELECT @DOE = [c].[DOE] FROM [deleted] [c];
SELECT @IppisNo = [c].[IppisNo] FROM [deleted] [c];
SELECT @DOLA = [c].[DOLA] FROM [deleted] [c];
SELECT @Conhess_Conmess = [c].[Conhess_Conmess] FROM [deleted] [c];
SELECT @Step = [c].[Step] FROM [deleted] [c];
SELECT @PD = [d].[PD] FROM [deleted] [d];
SELECT @mobile = [l].[Mobile] FROM [deleted] [l];
SELECT @Email_Address = [c].[Email_Address] FROM [deleted] [c];
SELECT @StaffNo = [c].[StaffNo] FROM [deleted] [c];
SELECT @Gl = [c].[Gl] FROM [deleted] [c];
SELECT @DeptID = [c].[DeptID] FROM [deleted] [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
Then your version with a single SELECT:
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 [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             = [c].[Title]
, @StaffId         = [c].[StaffId]
, @Surname         = [c].[Surname]
, @Firstname       = [c].[Firstname]
, @Middlename      = [c].[Middlename]
, @Othername       = [c].[Othername]
, @Gender          = [c].[Gender]
, @MaidenName      = [c].[CreatedDate]
, @DOB             = [c].[CreatedBy]
, @DOFA            = [c].[Title]
, @DOC             = [c].[DOC]
, @Origin          = [c].[Origin]
, @lg              = [c].[GLOE]
, @GLOE            = [c].[GLOE]
, @DOE             = [c].[DOE]
, @IppisNo         = [c].[IppisNo]
, @DOLA            = [c].[DOLA]
, @Conhess_Conmess = [c].[Conhess_Conmess]
, @Step            = [c].[Step]
, @PD              = [c].[PD]
, @mobile          = [c].[Mobile]
, @Email_Address   = [c].[Email_Address]
, @StaffNo         = [c].[StaffNo]
, @Gl              = [c].[Gl]
, @DeptID          = [c].[DeptID]
FROM
[deleted] [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
GO

Thanks bro ....i appreciate and many thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Your welcome, I think. Do you even understand any of this? If you had to write an UPDATE trigger, would know what you needed to do?