Friday, February 24, 2012

How to retrieve a value from the Inserted table

I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:

CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals]
AFTER INSERT
AS
Declare @.Portal int
set @.Portal = (select PortalId FROM inserted)

Declare @.TabId Int
set @.Tabid = (select TabID from Tabs where Tabs.PortalID = @.Portal
and Tabs.TabName = 'MyTab')

Declare @.ModuleId int
set @.ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @.TabId
and Modules.ModuleTitle = 'MyModule')

update Modules
set
AuthorizedEditRoles = '-1;'

where ModuleId = @.ModuleIdtry changing this:
set @.Portal = (select PortalId FROM inserted)

to this:
SELECT @.Portal=PortalID FROM Inserted|||Thanks for the advice, but I've tried that syntax too - without any luck.

I'm getting no compilation errors, so the sytax is OK. I don't have any fancy tools to check what value (if any) that is being returned from the Inserted table. The problem is somewhere else...|||if PortalID is an identity column then use

SET @.Portal = @.@.Identity

@.@.Identity is the value from the LAST record inserted so as long as your only inserting one record at a time, that should work.|||I've tried that too...and get an error in my application. Also, being a newbie, I don't know if PortalID is an identity column, and given that I get an error it might not be. Can I make it an identity column? If so, could that cause my app to break somewhere else?

Furthermore, I am only inserting one record at a time, but there might be other users performing the same transaction (although with other unique values being inserted).

hmm...?

No comments:

Post a Comment