General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
The smarter way to update row that just inserted?
I'm try to write sp that will update UserID on Inserted/Updated Row.
I wrote a trigger but I've to update code on all table.. So, I think to write this sp to make code more modular. But the problem is I must specify which row will update by use Primary Key Column like this:
CREATE TRIGGER Item_InsUpd ON Item
FOR INSERT, UPDATE
AS
UPDATE Item
SET UserID = SYSTEM_USER
FROM Item, Inserted
WHERE Item.ID = Inserted.ID
Then if I want this code can use with every table by write it down into Sp.
CREATE PROCEDURE usp_updateUserID
@Tablename nvarchar(128),
@pKey nvarchar(50)
AS
UPDATE @Tablename --Got a syntax error on this line!
SET UserID= SYSTEM_USER
FROM @TableName, Inserted
WHERE @pKey.ID = Inserted.ID --This line I'm not sure about syntax??
Problem on a varaity of primary key column that difference in each table ...That I must use WHERE... as:
WHERE Ap.ID = Inserted.ID
even a composite key like this:
WHERE Item.ID = Inserted.ID AND Price.ID = Inserted.ID
or
WHERE Doc.Docno = Inserted.Docno And Doc.Prefix = Inserted.Prefix
Main question is: It's have variety of key to use.
Second is: I can't use @Tablename after Update... Why??? Error say I'm not declare @Tablename yet???
How could a smarter way to use variable and parameter on Sp like this?
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only