Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 5th, 2005, 05:05 AM
Registered User
 
Join Date: Apr 2005
Location: Hong Kong, , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trigger problem

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

Reply With Quote
  #2 (permalink)  
Old May 5th, 2005, 11:57 PM
Registered User
 
Join Date: Apr 2005
Location: Hong Kong, , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old May 20th, 2005, 09:17 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with trigger in registration form prashant_telkar SQL Server 2000 2 July 9th, 2007 06:52 PM
Trigger Problem in sql server monika.vasvani SQL Language 1 March 1st, 2007 07:09 AM
problem in trigger of chapter 6 rashid.shaban@gmail.com BOOK: ASP.NET Website Programming Problem-Design-Solution 1 February 26th, 2007 11:45 PM
stored procedure problem with trigger helkayal SQL Server 2000 5 February 22nd, 2005 12:59 AM
Problem with update trigger gbrown SQL Language 2 September 4th, 2004 12:33 PM



All times are GMT -4. The time now is 10:37 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.