Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
The smarter way to update row that just inserted?
Message
De
14/12/2000 10:12:05
 
 
À
14/12/2000 05:05:58
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Divers
Thread ID:
00452399
Message ID:
00453338
Vues:
20
>Thank for last answer.. but new problem occur. I wrote this sp to call in Triggerr...
>==========================
>ALTER PROCEDURE usp_autoStamp2
> @TableName nvarchar(128), --ª×è͵ÒÃÒ§·Õè¨Ðá¡é(ºÑ§¤Ñº)
> @Key1 nvarchar(128) = NULL
>AS
> DECLARE @cSQL nvarchar(1000),
> @cWhere1 nvarchar(1000)
> IF @TableName IS NOT NULL
> SET @cSQL =
> 'UPDATE ' + @Tablename +
> ' SET UserID = SYSTEM_USER, LastUpdate = GETDATE() FROM Inserted, ' + @TableName
> ELSE
> BEGIN
> RAISERROR( ' ERROR! ', 16, 1)
> RETURN 1
> END
> IF @Key1 IS NOT NULL
> SET @cWhere1 = ' WHERE ' + @TableName +'.' + @Key1 +' = Inserted.' + @Key1
> ELSE
> BEGIN
> Declare @Message nvarchar(100)
> SET @Message = 'ERROR! no Key Column From '+ @TableName
> RAISERROR( @Message, 16, 1)
> END
> Execute(@cSQL + @cWHERE1)
>==========================
>HERE is result.....
>==========================
>Server: Msg 208, Level 16, State 1, Line 1
>Invalid object name 'Inserted'.
>
>Why I can't call Inserted table from SP inside Trigger!!
>As I very sure abut syntax as thlis statement...
>UPDATE Item SET UserID = SYSTEM_USER, LastUpdate = GETDATE() FROM Inserted, Item WHERE Item.ID = Inserted.ID
>If there're no way to reference to Inserted table outer Trigger. How could we use it?

The Inserted (and Deleted) tables only exist inside a Trigger - not a Stored Procedure.
I'm assuming that there are actual Triggers created for tables which then call this stored procedure. Is that correct?
Assuming that it is, I don't see why you need the Inserted table in this procedure, since you already have the Key1 value.
Try taking out the Inserted table from this procedure and see if that works.
HTH
Insanity: Doing the same thing over and over and expecting different results.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform