Trigger Syntax HELP!!

[TonyKhela]

Hi All,
I have two tables (see below) Employees _Live and tbl_Audit).
I am trying to get any changes on any field to be captured into the Audit table. See my Trigger code below.
However, I get a syntax error, can anyone help?
thx
TK


CREATE TABLE [dbo].[Employees_Live] (
[TelephoneDirID] [int] NOT NULL ,
[NTUserID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[LastName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[KnownAs] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[nvarchar] (129) COLLATE Latin1_General_CI_AS NOT NULL ,
[PhoneNo] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[FaxNo] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OtherNos] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Location] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[FloorLevel] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[JobTitle] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[JobDesc] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[JobProfile] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[ImagePath] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Grade] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EmployeeNo] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[IsActive] [int] NOT NULL ,
[Title] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[UnitID] [int] NULL ,
[BuildingID] [int] NULL ,
[LastChangedBy] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[IsChecked] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
[PublishState] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Audit] (
[AID] [int] IDENTITY (1, 1) NOT NULL ,
[TableName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Fieldname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Who] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[dateMod] [datetime] NULL ,
[PreviousValue] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[NewValue] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_Audit] ADD
CONSTRAINT [DF_tbl_Audit_dateMod] DEFAULT (getdate()) FOR [dateMod],
CONSTRAINT [PK_tbl_Audit] PRIMARY KEY CLUSTERED
(
[AID]
) ON [PRIMARY]
GO

CREATE TRIGGER Trg_Audit2
ON dbo.Employees_Live
FOR UPDATE
AS
DECLARE @TABLENAME VARCHAR(50)
DECLARE @FIELDNAME VARCHAR(50)
DECLARE @WHO VARCHAR(50)
DECLARE @DATEMOD DATETIME
DECLARE @PREVIOUSVALUE VARCHAR(50)
DECLARE @NEWVALUE VARCHAR(50)
DECLARE @COL_NAME VARCHAR(50)

SET @TABLENAME='Employees_Live'
SET @Who=suser_sname()
SET @DATEMOD=GETDATE()


DECLARE COL_NAMES CURSOR
FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='EMPLOYEES_LIVE'
OPEN COL_NAMES

FETCH NEXT FROM COL_NAMES INTO @FIELDNAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF UPDATE(@FIELDNAME)
BEGIN
SELECT @PREVIOUSVALUE=@FIELDNAME FROM DELETED
SELECT @NEWVALUE=@FIELDNAME FROM INSERTED

INSERT tbl_Audit (TableName
,Fieldname
, Who
, dateMod
, PreviousValue
, NewValue)
VALUES
(@TableName
,@Fieldname
, @Who
, @dateMod
, @PreviousValue
, @NewValue)
END

FETCH NEXT FROM COL_NAMES INTO @FIELDNAME
END

CLOSE COL_NAMES
DEALLOCATE COL_NAMES

 

[tlbroadbent]

You cannot use a variable for a column name.

SELECT @PREVIOUSVALUE=@FIELDNAME FROM DELETED

@FIELDNAME is not allowed.



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[TonyKhela]

OK. How do I get round this?
thx
TK

 

[tlbroadbent]

Some people would be tempted to use dynamic SQL but advise against that approach. I recommend creating an insert statement for each column in the Employees_Live table. Avoid cursors in triggers.

Sample code:

  INSERT tbl_Audit
  (TableName, Fieldname, Who, dateMod,
   PreviousValue, NewValue)
  VALUES (@TableName, 'NTUserID', @Who,
          @dateMod, d.NTUserID, i.NTUserID)

  INSERT tbl_Audit
  (TableName, Fieldname, Who, dateMod,
   PreviousValue, NewValue)
  VALUES (@TableName, 'FirstName', @Who,
          @dateMod, d.FirstName, i.FirstName)

  INSERT tbl_Audit
  (TableName, Fieldname, Who, dateMod,
   PreviousValue, NewValue)
  VALUES (@TableName, 'LastName', @Who,
          @dateMod, d.LastName, i.LastName)
...

BTW: Your approach would work if only one row at a time was updated. However, it would not record updates to multiple rows in the same query.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm