Scope of Deleted table in Trigger

Scope of Deleted table in Trigger

Post by Ste » Fri, 21 Feb 2003 20:26:53



Hi
I am trying to create a generic audit trigger that will store the old value
and new value of a column when it is updated.
I dont want to have to add to this trigger when I add new columns to my
customer table, therefore I have to use sp_executesql to retrieve the old
value from the deleted table.
Problem is that the Deleted table does not appear to exist in the scope of
the sp_executesql statement, but only in the trigger.

See the code below.

But Select MyFifthColumnName from Deleted  works fine.

Can anyone see how I can retrieve this one value, without have to change my
trigger everytime I add a new column to this table???
Thanks
Steve

TABLE CREATES

CREATE TABLE [dbo].[Customer_Audit_T] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [URN_IN] [int] NOT NULL ,
 [Time_DT] [smalldatetime] NOT NULL ,
 [Amendment_Type_CH] [char] (1) NOT NULL ,
 [Field_Name_VC] [varchar] (30) NOT NULL ,
 [Old_Value_VC] [varchar] (100) NULL ,
 [New_Value_VC] [varchar] (100) NOT NULL ,
 [Program_Name_VC] [varchar] (20) NOT NULL ,
 [User_VC] [varchar] (20) NOT NULL
)
CREATE TABLE [dbo].[UpdateThis] (
 [URN] [int] IDENTITY (1, 1) NOT NULL ,
 [A] [char] (10) NULL ,
 [B] [char] (10) NULL ,
 [C] [char] (10) NULL ,
 [D] [char] (10) NULL ,
 [E] [char] (10) NULL ,
 [F] [char] (10) NULL ,
 [G] [char] (10) NULL
)

Attempt at trigger!!!

CREATE TRIGGER trigUpdCustomer

ON UpdateThis

FOR update AS












--find the number of columns in the table

    FROM syscolumns INNER JOIN sysObjects ON SysColumns.ID = sysObjects.ID

--get the username

--loop round to check which columns have been updated


  Begin


    Begin


sysObjects ON SysColumns.ID = sysObjects.ID

      --Select MyFifthColumnName from Deleted  (THIS WORKS)


Deleted'

      --THIS FAILS

      Insert Into Customer_Audit_T (URN_IN,Time_DT, Amendment_Type_CH,
Field_Name_VC, Old_Value_VC, New_Value_VC, Program_Name_VC, User_VC)


    End

  End

 
 
 

Scope of Deleted table in Trigger

Post by Ste » Fri, 21 Feb 2003 20:28:52


Forgot to say
I use SQL7 sp3 on NT4.

 
 
 

Scope of Deleted table in Trigger

Post by Uri Diman » Fri, 21 Feb 2003 20:56:18


Ste D
I hope the following primitive example will help you to start
(I know it is not solution for you but look at this and i hope you will get
some idea)

create table test_1
(
 col1 int not null,
 col2 int not null,
 col3 int not null
)

create table test_audit
(
 col1_new int not null,
 col2_new int not null,
 col3_new int not null,
 col1_old int not null,
 col2_old int not null,
 col3_old int not null,
 col_date datetime default getdate()
)

insert into test_1 values (1,2,3)

create trigger my_trig on test_1
for update
as
insert into test_audit (col1_new,col2_new,col3_new,
                        col1_old,col2_old,col3_old)
select * from inserted i join deleted d on i.col1=d.col1

update test_1 set col2=4 where col1=1
select * from test_audit

drop table test_1
drop table test_audit


> Hi
> I am trying to create a generic audit trigger that will store the old
value
> and new value of a column when it is updated.
> I dont want to have to add to this trigger when I add new columns to my
> customer table, therefore I have to use sp_executesql to retrieve the old
> value from the deleted table.
> Problem is that the Deleted table does not appear to exist in the scope of
> the sp_executesql statement, but only in the trigger.

> See the code below.

> But Select MyFifthColumnName from Deleted  works fine.

> Can anyone see how I can retrieve this one value, without have to change
my
> trigger everytime I add a new column to this table???
> Thanks
> Steve

> TABLE CREATES

> CREATE TABLE [dbo].[Customer_Audit_T] (
>  [ID] [int] IDENTITY (1, 1) NOT NULL ,
>  [URN_IN] [int] NOT NULL ,
>  [Time_DT] [smalldatetime] NOT NULL ,
>  [Amendment_Type_CH] [char] (1) NOT NULL ,
>  [Field_Name_VC] [varchar] (30) NOT NULL ,
>  [Old_Value_VC] [varchar] (100) NULL ,
>  [New_Value_VC] [varchar] (100) NOT NULL ,
>  [Program_Name_VC] [varchar] (20) NOT NULL ,
>  [User_VC] [varchar] (20) NOT NULL
> )
> CREATE TABLE [dbo].[UpdateThis] (
>  [URN] [int] IDENTITY (1, 1) NOT NULL ,
>  [A] [char] (10) NULL ,
>  [B] [char] (10) NULL ,
>  [C] [char] (10) NULL ,
>  [D] [char] (10) NULL ,
>  [E] [char] (10) NULL ,
>  [F] [char] (10) NULL ,
>  [G] [char] (10) NULL
> )

> Attempt at trigger!!!

> CREATE TRIGGER trigUpdCustomer

> ON UpdateThis

> FOR update AS












> --find the number of columns in the table

>     FROM syscolumns INNER JOIN sysObjects ON SysColumns.ID = sysObjects.ID

> --get the username

> --loop round to check which columns have been updated


>   Begin


>     Begin


> sysObjects ON SysColumns.ID = sysObjects.ID

>       --Select MyFifthColumnName from Deleted  (THIS WORKS)


> Deleted'

>       --THIS FAILS

>       Insert Into Customer_Audit_T (URN_IN,Time_DT, Amendment_Type_CH,
> Field_Name_VC, Old_Value_VC, New_Value_VC, Program_Name_VC, User_VC)


>     End

>   End