Need a trigger?

[tbode2006]

CREATE PROCEDURE [dbo].[spUpdatePrimeTime]
        (       @pt     SMALLINT,               --new pump prime time
                @fc     SMALLINT,               --{FILTER} filter code [1-4]
                @cc     SMALLINT,               --{FILTER} clean cycle code [1-4]
                @fa     SMALLINT,               --{FILTER} system family code [0-16]
                @ps     SMALLINT,               --{FILTER} robot pass code[1-3]
                @ac     SMALLINT)               --{FILTER} activity status code [1-3]
AS

DECLARE @myCMD as varchar(2000) , @myFilter1 as varchar(100), @myFilter2 as varchar(100), @myFilter3 as varchar(100) ,@myFilter4 as varchar(100), @myFilter5 as varchar(100)

SET @myCMD = 'UPDATE tblPaint_Properties SET PRIME_TIME = '  + STR(@pt) + ', CHANGE_DATE = getdate()  WHERE(GLOBAL_UPDATE=1) '

IF (@fc <> 4) or (@cc <> 4) or (@fa > 0) or (@ps <> 3) or (@ac <> 2)
        BEGIN
        SET @myFilter1 = ''
        SET @myFilter2 = ''
        SET @myFilter3 = ''
        SET @myFilter4 = ''
        SET @myFilter5 = ''
        SET @myCMD = @myCMD + '  and'

        IF @fc <> 4 SET @myFilter1 = '  (FILTER_CODE = ' + STR(@fc) +') and'
        IF @cc <> 4 SET @myFilter2 = '  (CLEAN_CYCLE_CODE = ' + STR(@cc) + ') and'
        IF @fa <> 0 SET @myFilter3 = '  (FAMILY = ' + STR(@fa) + ') and'
        IF @ps <> 3 SET @myFilter4 = '(PASS = ' + STR(@ps) + ') and'
        IF @ac <> 2 SET @myFilter5 = '(COLOR_ACTIVE = ' + STR(@ac) + ') and'

        IF @myFilter1 <> '' SET @myCMD = @myCMD + @myFilter1
        IF @myFilter2 <> '' SET @myCMD = @myCMD + @myFilter2
        IF @myFilter3 <> '' SET @myCMD = @myCMD + @myFilter3
        IF @myFilter4 <> '' SET @myCMD = @myCMD + @myFilter4
        IF @myFilter5 <> '' SET @myCMD = @myCMD + @myFilter5

        --Remove the last AND
        SET @myCMD = LEFT(@myCMD, LEN(@myCMD)-4)
        END

EXEC (@myCMD)
RETURN @@ROWCOUNT
GO

The above procedure updates a numeric value in one table based on variable filtering options. I have been asked to create an audit log of what entries were changed to a new value and when. Below is my audit log table script

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAudit_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAudit_Log]
GO

CREATE TABLE [dbo].[tblAudit_Log] (
        [myTable] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [myPaintCode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [myField] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [myOldValue] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [myNewValue] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [myDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

My thought is that I need to define a trigger on UPDATE of a specific column. However, I need a value from another column for my audit log table. The myTable field will be a constant, the myField will be a constant, the myPaintCode is the field that I need from the original table, the my OldValue will be a constant, the myNewValue is passed to the stored procedure, and myDate is set to initial value of getdate() in the table definition. How do I proceed?

 

[Qik3Coder]

Triggers have two "magic" tables, one called inserted and one called deleted. These tables mirror the table that the insert trigger is on. These tables show you what is going in, and what is being taken out.

in Query Analyzer check out the default Create Trigger (for update)

you have access to all the tables in your database, so if you need to go get a value from a different table you can go get it.

-The answer to your problem may not be the answer to your question.