Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Titre:
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?
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement