0

I posted a question yesterday and this is related. I have a table called People_Master

CREATE TABLE [dbo].[People_Master](
[ID] [int] IDENTITY(900001,1) NOT NULL,
[Email] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[uac] [int] NULL,
[department] [nvarchar](50) NULL,
 CONSTRAINT [PK_People_Master] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

And I have created another table called People_Delta

CREATE TABLE [dbo].[People_Delta](
[ID] [int] NOT NULL,
[Email] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[uac] [int] NULL,
[Department] [nvarchar](50) NULL,
[change_type] [nvarchar](10) NULL
) ON [PRIMARY]

Notice that I have one additional column in People_Delta called "change_type"

Now I'm trying to write an update trigger for People_Master so that when an update happens on it, it should write that entire row to People_Delta and set the change_type to "Modify".

Here is the trigger I have written (I'm not good at SQL. Lol!)

CREATE TRIGGER [dbo].[TR_Update] 
   ON  [dbo].[People_Master]
   AFTER Update
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @RowCount int
    Declare @ID int

    --Create a temp table to hold the inserted data.
    Declare @TempTable As Table
    (
        [ID] [int],
        [Email] [nvarchar](50) ,
        [FirstName] [nvarchar](50),
        [uac] [int] ,
        [department] [nvarchar](50)     
    )

    Insert Into @TempTable (ID,Email,FirstName,uac,department)
    select 
        ID,
        Email,
        FirstName,
        uac,
        department
    from
        inserted    


    select @ID = ID from inserted

    --check if the row for this ID doesnt exist already.
    Select @RowCount=COUNT(*) from People_Delta where People_Delta.ID = @ID and People_Delta.change_type = 'Modify';

    if(@RowCount = 0)
    Begin
        Insert into People_Delta (ID,Email,FirstName,uac,Department,change_type)
        values (@TempTable.ID,@TempTable.Email,@TempTable.FirstName,@TempTable.uac,@TempTable.department,'Modify');
    END

END

GO

However, when I execute this trigger, I get the following error:

Msg 137, Level 16, State 1, Procedure TR_Update, Line 50
Must declare the scalar variable "@TempTable".
Msg 137, Level 16, State 1, Procedure TR_Update, Line 50
Must declare the scalar variable "@TempTable".
Msg 137, Level 16, State 1, Procedure TR_Update, Line 50
Must declare the scalar variable "@TempTable".
Msg 137, Level 16, State 1, Procedure TR_Update, Line 50
Must declare the scalar variable "@TempTable".
Msg 137, Level 16, State 1, Procedure TR_Update, Line 50
Must declare the scalar variable "@TempTable".

What am I doing wrong ? Please help! GT.

gtrivedi
  • 435
  • 1
  • 7
  • 18
  • Also perhaps based on my requirements, is there a better way to accomplish this? Seems to me the problem with above is that if I lets say have to change my table design, like add a new column, I will have to change my trigger definition to include that column in the update for People_Delta too. – gtrivedi Jun 12 '15 at 09:11
  • If you want to add more to your question, you can [edit] it. You shouldn't ever need to add a comment directly on your own questions (or answers) – Damien_The_Unbeliever Jun 12 '15 at 09:16
  • maybe this might help? http://stackoverflow.com/questions/20265023/must-declare-the-table-variable-name-in-stored-procedure – Jerry Tai Jun 12 '15 at 09:17

1 Answers1

0

Try this trigger.

Changed this insert query 

 Insert into People_Delta (ID,Email,FirstName,uac,Department,change_type)
        values (@TempTable.ID,@TempTable.Email,@TempTable.FirstName,@TempTable.uac,
                @TempTable.department,'Modify');

to

Insert into People_Delta (ID,Email,FirstName,uac,Department,change_type)
        select  ID, Email,FirstName, uac, department, 'Modify'    from
        inserted

Updated Trigger:

CREATE TRIGGER [dbo].[TR_Update] 
   ON  [dbo].[People_Master]
   AFTER Update
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @ID int

    select @ID = ID from inserted

    If NOT EXISTS(Select People_Delta.ID from People_Delta where People_Delta.ID = @ID 
                       and People_Delta.change_type = 'Modify')
    BEGIN
         Insert into People_Delta (ID,Email,FirstName,uac,Department,change_type)
          select  ID, Email,FirstName, uac, department, 'Modify'    from
          inserted  
    END
END
Saravana Kumar
  • 3,669
  • 5
  • 15
  • 35