Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
The smarter way to update row that just inserted?
Message
De
12/12/2000 13:54:13
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Titre:
The smarter way to update row that just inserted?
Divers
Thread ID:
00452399
Message ID:
00452399
Vues:
70
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
Fil
Voir

Click here to load this message in the networking platform