Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
The smarter way to update row that just inserted?
Message
From
14/12/2000 10:12:05
 
 
To
14/12/2000 05:05:58
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Miscellaneous
Thread ID:
00452399
Message ID:
00453338
Views:
19
>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.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform