April 8, 2002 at 3:49 pm
Hi,
I am building a trigger to log insert into a Audit table. What is a proper syntax to reference Old and new values. I come from Oracle where it was simple new. and old. It does not seem to work
I would like to pass a new parameter to a stored procedure - something like this:
EXEC sp_LogAuditTrail :new.LogID
That doesn't work
Then I tried this:
CREATE TRIGGER [tr_tProjectLog_ProfSDGs] ON [dbo].[tProjectLog]
AFTER INSERT
AS
DECLARE @LogID Int,
@Profile Int
Declare @Note varchar(800)
SEt NOCOUNT ON
--look up all affected SDG fro the profile
SElect LogID, Profile, Note From inserted
into @LogID, @Profile, @Note
EXEC sp_LogAuditTrail @LogID, @Profile, @Note
It seems that Select into also doesnt work.
What is the correct way to do this?
Thanks.
April 8, 2002 at 3:58 pm
Try this.
CREATE TRIGGER UrTrName ON [dbo].[tblName]
FOR INSERT
AS
DECLARE @LogID Int
DECLARE @Profile Int
DECLARE @Note varchar(800)
SET NOCOUNT ON
--look up all affected SDG fro the profile
SELECT @LogID = LogID, @Profile = Profile, @Note = Note FROM inserted
EXEC sp_LogAuditTrail @LogID, @Profile, @Note
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 9, 2002 at 8:01 am
I tried that but it dosent work. The LogID is an identity column. Is it available as part of insert values or do I have to get it with 'SELECT ident_current...'?
Also, none of the variables in the trigger are populated in the Trace
This is how it looks in trace:
-- tr_tProjectLog_ProfSDGs
EXEC sProfLogActiveSDGs @LogID, @Profile, @Note
Shouldn't there be actual values instead of the @variables?
Thanks.
April 9, 2002 at 10:48 am
April 9, 2002 at 10:57 am
Also in the trace what EVENTS are you choosing. Looks like the build events. Use the completed instead for TSQL and SP items.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 9, 2002 at 11:34 am
The identity value is already available in the trigger. I send myself an email from the trigger (code below) and it has the right value. So no need for scope_identity()
I used the StmtCompleted in trace but still no varialbe values ?????, just for the trigger and the called SP. Other SPs run fine (there is a DTS running concurrently)
Why???
----Trigger CODE----
ALTER TRIGGER [tr_tProjectLog_ProfSDGs] ON [dbo].[tProjectLog]
AFTER INSERT
AS
DECLARE @LogID Int
DECLARE @Profile Int
DECLARE @Note varchar(800)
Declare @Mess varchar(20)
SET NOCOUNT ON
--look up all affected SDG fro the profile
SELECT @LogID = LogID, @Profile = Profile, @Note = Note FROM inserted
Set @Mess =CAST(@LogID as varchar(20))
EXEC [master].[dbo].[xp_sendmail] @recipients='user@doamin.com', @subject='Test', @message=@Mess
EXEC sProfLogActiveSDGs @LogID, @Profile, @Note
April 9, 2002 at 12:52 pm
Figured it out!
The note field in the trigger was null. SP did not like that.
It is firing now.
Thank you all for help 🙂
April 22, 2002 at 5:24 pm
"deleted" and "inserted" are not exact but equivalent to Oracle's :old and :new.
But both deleted and inserted are tables unlike :old and :new.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply