Hi,

I get a problem when I am writing a trigger in SQL server. The trigger is doing a audit log function. While a record is update, the trigger will search for another table which stores all the fields and table that need to be log. But when I store the field's name into an @local_variable, It return an error at IF UPDATE(@local_valiable)... The return message said the syntax error is "Incorrect syntax near '@local_variable'".

Because the field is dynamic pack from another table. I cannot use IF COLUMNS_UPDATE() function....

If there any one have solution about it? Thanks~

Kitkits

May be I write out the code to show the problems:
consider the below 2 tables:

CREATE TABLE [dbo].[tbl_action] (
    [action_id] [varchar] (20) ,
    [description] [varchar] (50),
    [last_updated_date] [datetime],
    [last_updated_by] [varchar] (15),
    [created_date] [datetime],
    [created_by] [varchar] (15)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_audit_criteria] (
    [table] [varchar] (30),
    [audit_field] [varchar] (50)
) ON [PRIMARY]
GO

-- Test data
INSERT INTO tbl_audit_criteria VALUES ('tbl_action', 'action_id')
INSERT INTO tbl_audit_criteria VALUES ('tbl_action', 'description')
-- Test data end

tbl_action is the one a create the trigger in. tbl_audit_criteria use to store the table name and the field needs to be audit. Then I write a simple trigger for testing at the tbl_action:

CREATE TRIGGER tr_update_log
ON tbl_action
FOR UPDATE, DELETE
AS

DECLARE @field_name VARCHAR(50)
DECLARE @table_name VARCHAR(30)
SET @table_name = 'tbl_action'
DECLARE @action_type VARCHAR(6)
DECLARE @action_by VARCHAR(6)
DECLARE @preimage VARCHAR(300)
DECLARE @postimage VARCHAR(300)
DECLARE @tmpsql VARCHAR(4000)
DECLARE @tmpval VARCHAR(255)

DECLARE fields_cursor CURSOR
FOR
   SELECT audit_field FROM t_audit_criteria WHERE t_audit_criteria.[table]=@table_name
OPEN fields_cursor

FETCH NEXT FROM fields_cursor INTO @field_name

WHILE (@@FETCH_STATUS<>-1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
-- Here's got the problem:
-- These two lines say the variable @preimage must be declare
    exec('SELECT @preimage=' +@field_name+' FROM inserted')
    exec('SELECT @postimage=' +@field_name+' FROM deleted')
-- These two lines it assign @preimage and @postimage's value become "@field_name"
    SELECT @preimage=@field_name FROM inserted
    SELECT @postimage=@field_name FROM deleted

    print 'field=' + @field_name
    print 'pre=' + @preimage
    print 'post=' + @postimage

   END
   FETCH NEXT FROM fields_cursor INTO @field_name
END
CLOSE fields_cursor
DEALLOCATE fields_cursor

GO

Could you resolve this issue. Even I'm trying to use the local variable in the If UPDATE clause of the trigger, and I get the same error. Please let me know if you resolved the problem.

Thanks.