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.