February 26, 2003 at 10:12 am
Hello...been awhile...
I have a SProc which adds a new record to a table and also proceeds to update the same new record with the new "Identity". One of the colums is a self-join - essentially a table of accounts and one of the columns is the Account to be debited - which points back to the Identity column of the same table. I also have my own event logging in place in which triggers fore to store Updates, Deletes, etc...
Now, When I "Update the new record", I don' want to log the event since it's a system override on my part. So, Before I do the Update in the SProc, I disable the Event logging trigger, update the column to the new ID, then re-enable the trigger.
Problem is, For me as sysadmin - I can disable and enable triggers but normal users cannot. But, the updating happens in a SProc for which I have complete control. I'd like to be able to use the normal users rights to "Execute" the SProc, but once inside, use admin rights to edit the triggers. Here's a sample SP:
CREATE PROCEDURE dbo.AccountInsert
(
@AccountHeaderNme varchar(60),
@AccountType int,
@DebitFeeAccount int = NULL
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO cdAccount
(
AccountHeaderNme,
AccountType,
DebitFeeAccount
)
VALUES
(
@AccountHeaderNme,
@AccountType,
@DebitFeeAccount
)
/*
I'd like Manually override here and somehow change to a sysadmin or other power user.
Then any user with Execute SP rights can run this SP properly.
*/
IF @ID_DebitFeeAccount IS NULL
BEGIN
/* Disable the System Event Audit Trigger since this
is a manual override and not a "User Update" */
ALTER TABLE dbo.cdAccount DISABLE TRIGGER tU_cdAccount_Update_Notification
DECLARE @NEW_AccountID int
SET @NEW_AccountID = SCOPE_IDENTITY()
UPDATE cdAccount
SET DebitFeeAccount = @NEW_AccountID
WHERE id_Account = @NEW_AccountID
ALTER TABLE dbo.cdAccount ENABLE TRIGGER tU_cdAccount_Update_Notification
END
GO
Anyone have any suggestions ????
- B
February 26, 2003 at 10:54 am
I would not recommend going this route. It is "tricky" and not "clean".
If you need special behavior in the trigger for the updated records, create a flag column in your table and set it as part of your UPDATE. Then check for the flag in the beginning of the trigger and skip whatever you need to in the trigger.
Of course, this is just one possible solution. There could be more.
Michael
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply