Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
The smarter way to update row that just inserted?
Message
From
12/12/2000 13:54:13
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
The smarter way to update row that just inserted?
Miscellaneous
Thread ID:
00452399
Message ID:
00452399
Views:
69
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
Map
View

Click here to load this message in the networking platform