By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

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

P: 14
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.
Mar 25 '13 #1
Share this Question
Share on Google+
6 Replies


P: 3
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
Mar 25 '13 #2

Rabbit
Expert Mod 10K+
P: 12,430
And what trouble are you having creating your trigger?
Mar 25 '13 #3

P: 14
I've tried somthing like following:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER Super 
  2.  
  3. ON Products 
  4.  
  5. AFTER INSERT, UPDATE
  6.  
  7. AS
  8.  
  9. UPDATE Product_audit (Column1,Column2, Column3, ... )
  10.  
  11. SELECT (Column1,Column2,Column3,...)
  12.  
  13. 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 ?
Mar 26 '13 #4

Rabbit
Expert Mod 10K+
P: 12,430
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.
Mar 26 '13 #5

P: 14
Created following trigger:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER Super1
  2.  
  3.     ON Products
  4.  
  5.     AFTER Update
  6.  
  7.     AS
  8.  
  9.     BEGIN
  10.  
  11.     DECLARE @ProductID int,
  12.              @ProductName nvarchar,
  13.             @SupplierID varchar,
  14.             @CategoryID int,
  15.             @QuantityPerUnit int,
  16.             @UnitPrice money,
  17.             @UnitsInStock smallint,
  18.             @UnitsOnOrder smallint,
  19.             @ReorderLevel smallint,
  20.             @Discontinued bit
  21.  
  22.  
  23.     SELECT @ProductID=ProductID,
  24.            @ProductName=ProductName, 
  25.            @SupplierID=SupplierID,
  26.            @CategoryID=CategoryID,
  27.            @QuantityPerUnit=QuantityPerUnit, 
  28.            @UnitPrice=UnitPrice,
  29.            @UnitsInStock=UnitsInStock, 
  30.            @UnitsOnOrder=UnitsOnOrder, 
  31.            @ReorderLevel=ReorderLevel, 
  32.            @Discontinued=Discontinued  
  33.  
  34.    FROM Products
  35.  
  36.  
  37.  
  38.    UPDATE Products_audit  
  39.  
  40.    SET     ProductID=@ProductID,
  41.            ProductName=@ProductName, 
  42.            SupplierID=@SupplierID,
  43.            CategoryID=@CategoryID,
  44.            QuantityPerUnit=@QuantityPerUnit, 
  45.            UnitPrice=@UnitPrice,
  46.            UnitsInStock=@UnitsInStock, 
  47.            UnitsOnOrder=@UnitsOnOrder, 
  48.            ReorderLevel=@ReorderLevel, 
  49.            Discontinued=@Discontinued
  50.  
  51.  
  52.     END
  53.  
Could anybody take a look at that and correct me if I was wrong anywhere ?
Mar 27 '13 #6

Rabbit
Expert Mod 10K+
P: 12,430
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.
Mar 27 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.