Create triggers for one table to update audit table with date and time and name

[gelezniyden]

I have one table Products with 10 columns on which need create trigger(s) to update changes in data in another table Products_audit which has 10 same columns plus two additional columns "Date" и "User" where need to insert date and name of user who made changes.

[Escargot18]

The way I do it is to create a form and open the table in datasheet view in the form. It sill looks like the table to users, but you can add an after update event to move the data along with the data and user infor into the new table. I'm sure there are better ways to do this, but this way works.
John

[Rabbit]

And what trouble are you having creating your trigger?

[gelezniyden]

I've tried somthing like following:

CREATE TRIGGER Super 

ON Products 

AFTER INSERT, UPDATE

AS

UPDATE Product_audit (Column1,Column2, Column3, ... )

SELECT (Column1,Column2,Column3,...)

FROM Products

Receiving following: Server: Msg 170, Level 15, State 31, Procedure Super, Line 20
Line 20: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Procedure Super, Line 24
Line 24: Incorrect syntax near ','.

I'm not so strong else in triggers creation could you please correct me I'd like to know where was I wrong ?

[Rabbit]

You have no line 20 and no line 24 in the code you posted. Unless you post your actual code, there's no way to tell you where the code went wrong.

[gelezniyden]

Created following trigger:

CREATE TRIGGER Super1

    ON Products

    AFTER Update

    AS

    BEGIN

    DECLARE @ProductID int,
     	    @ProductName nvarchar,
    	    @SupplierID varchar,
    	    @CategoryID int,
    	    @QuantityPerUnit int,
    	    @UnitPrice money,
    	    @UnitsInStock smallint,
    	    @UnitsOnOrder smallint,
    	    @ReorderLevel smallint,
    	    @Discontinued bit
    	

    SELECT @ProductID=ProductID,
           @ProductName=ProductName, 
           @SupplierID=SupplierID,
           @CategoryID=CategoryID,
           @QuantityPerUnit=QuantityPerUnit, 
           @UnitPrice=UnitPrice,
           @UnitsInStock=UnitsInStock, 
           @UnitsOnOrder=UnitsOnOrder, 
           @ReorderLevel=ReorderLevel, 
           @Discontinued=Discontinued  

   FROM Products



   UPDATE Products_audit  

   SET     ProductID=@ProductID,
           ProductName=@ProductName, 
           SupplierID=@SupplierID,
           CategoryID=@CategoryID,
           QuantityPerUnit=@QuantityPerUnit, 
           UnitPrice=@UnitPrice,
           UnitsInStock=@UnitsInStock, 
           UnitsOnOrder=@UnitsOnOrder, 
           ReorderLevel=@ReorderLevel, 
           Discontinued=@Discontinued
  

    END

Could anybody take a look at that and correct me if I was wrong anywhere ?

[Rabbit]

This code can't be the exact code that created those error messages you posted earlier. There's no parentheses anywhere and your error message said there was an error by a parentheses.

At this point, you have to redescribe your problem because it's no longer the same problem you wanted help with.

One thing that is wrong that I noticed right off the bat is that you're setting your values using every row in the table. And you're updating every row in your audit table. Pretty sure you didn't mean to do that.