TSql Syntax Using Set and EXEC

[stsuing]

The trigger I made for auditing a table uses an INSERT to do the auditing. Because of the permissions I can not do an insert and must use a stored procedure.

How can I Convert this to not use the INSERT and instead EXEC a stored procedure.

INSERT INTO [dbo].[GlassUserAudit]
( [ChangedUserId],
[ChangedByUserId],
[FieldName],
[OldValue],
[NewValue])
select I.[GlassUserRecordID], i.[LastEditedBy], 'RacfUserid', D.[RACFUserID] as old,i.[RACFUserID] as new
from inserted I
inner join deleted D on I.[GlassUserRecordID] = D.[GlassUserRecordID] where I.[RACFUserID] <> D.[RACFUserID]

This is where I am so far...

Declare @ChangedUserId int, @ChangedByUserId int
Declare @FieldName Varchar(50) , @OldValue Varchar(50), @NewValue Varchar(50)

--THIS IS THE PROBLEM AREA, so it's unfinished
Set @ChangedUserId = [GlassUserRecordID],
@ChangedByUserId ,
@FieldName ,
@OldValue ,
@NewValue
from
( select I.[GlassUserRecordID], i.[LastEditedBy], 'RacfUserid', D.[RACFUserID] as old,i.[RACFUserID] as new
from inserted I
inner join deleted D on I.[GlassUserRecordID] = D.[GlassUserRecordID] where I.[RACFUserID] <> D.[RACFUserID]
) aa


EXEC GlassUserAuditCreate @ChangedUserId,@ChangedByUserId,@FieldName,@OldValue,@NewValue

 

[NoCoolHandle]

Ok, first... permissions ... Does the user that caused the trigger have permissions on the table you need to do the insert to...

Object permission tend to travel through.. .this would mean that you need to explicitly grant permissons to the stored proc...

Re the trigger... If you are doing an insert there is no Deleted table, that only exists for deletes and updates.

To DEBUG a trigger..
Create a bs storedproc that performs the action you want to test and then on QA select debug.. At that point you can step through (1 line at a time) your trigger.

Rob

 

[stsuing]

This was strictly a Syntax problem. I don't want the user to have Insert permissions on the table. So I have to change my Trigger not to use an Insert.

There is a deleted table on an update. I believe the mechanics of an update actually deletes and inserts the rows under the hood

Here is the working solution

--By setting this to null before each call I can tell if the audit should happen
set @ChangedUserId = null
select @ChangedUserId=I.[GlassUserRecordID],
@ChangedByUserId=i.[LastEditedBy],
@FieldName='RacfUserid',
@OldValue=D.[RACFUserID],
@NewValue=i.[RACFUserID]
from inserted I
inner join deleted D on I.[GlassUserRecordID] = D.[GlassUserRecordID] where I.[RACFUserID] <> D.[RACFUserID]

if @ChangedUserId is not null
EXEC GlassUserAuditCreate @ChangedUserId,@ChangedByUserId,@FieldName,@OldValue,@NewValue