Create Trigger on Conditional Logic
-
I'm trying to create an update table script that will run on two different clients (A & B) databases. If it is Client A, I need to drop a specific trigger (or temporarily disable the trigger), update the table, and then recreate the trigger. For Client B, I just need to update the table (as the trigger does not exist on the table). The following script below is what I'm trying to accomplish, but it does not work. Trying to figure out how to script this:
DECLARE @AIsOwner bit
set @AIsOwner=0
/** Only delete if client A **/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NonUniqueITIN]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
drop trigger [dbo].[NonUniqueITIN]
set @AOwner=1
END
/** Update a table for both clients **/
UPDATE PersonTable SET personType=1
GO
if (@AOwner=1)
BEGIN
/**** Create the Trigger for Client A ***/
CREATE TRIGGER [dbo].[NonUniqueITIN]
ON [dbo].[PersonTable]
FOR UPDATE, INSERT
As
IF
EXISTS
(
SELECT DISTINCT
i.*
FROM
Inserted i
INNER JOIN [PersonTable] tbl
on i.id = tbl.id
WHERE
i.id <> tbl.id
)
BEGIN
RAISERROR('Non-unique ITIN', 16, 1)
ROLLBACK TRAN
END
GO
END
In addition to B not having the trigger, I can also differentiate between client A and B by a specific value in a database table both clients have (ProgramOwner table SELECT owner FROM TblProgramOwner (where a return value of A is clioent A and B is client B).
Any help is greatly appreciated.
-
The variable @AOwner is no longer exists after the UPDATE statement, as you terminate the batch with a GO statement. If you can determine the client by querying the database, that is a better way to go.
-
The trouble is that it always wants the CREATE Trigger statement immediately after a GO statement. I'm not sure how I could do a conditional check and then run the CREATE TRIGGER statement (is there some way to break out of the entire TSQL if the Owner is B?)
-
Not the prettiest thing I have ever seen, but how about creating the trigger, then checking if you have to keep it depending on the client and dropping it again accordingly?
Or have 2 separate install scripts for the 2 clients.
-
I could separately the install scripts, but future requirements dictate I'll have to do many similar items and the tracking becomes quite cumbersome. Your first idea sounds quite plausible - shall give it a shot.
-
Why don't you DISABLE/ENABLE the trigger instead of dropping it:
The trigger still exists but will not fire!
/** Only disable if client A **/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NonUniqueITIN]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
DISABLE TRIGGER NonUniqueITIN on PersonTable
set @AOwner=1
END
/** Update a table for both clients **/
UPDATE PersonTable SET personType=1
GO
if (@AOwner=1)
BEGIN
ENABLE TRIGGER NonUniqueITIN on PersonTable
END
Alternatively, we have a situation where we only want to bypass the trigger in a certain context. For this case we wrote a bypass trigger function:
CREATE
FUNCTION [dbo].[BypassTrigger] (@strTrigger varchar(128))
RETURNS
int AS
BEGIN
--Determines whether a given trigger is to be bypassed in this session
DECLARE @nBypass int
DECLARE @vTrigger varbinary(128)
SELECT @vTrigger = context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid
if @vTrigger = cast(@strTrigger as varbinary(128))
SELECT @nBypass = 1
else
SELECT @nBypass = 0
RETURN(@nBypass)
END
Then in the trigger we add:
ALTER
TRIGGER [dbo].[NonUniqueITIN ] on [dbo].[PersonTable]
for
INSERT, UPDATE
NOT
FOR REPLICATION
as
-- if the trigger is currently bypassed for this process, exit
IF
dbo.BypassTrigger(OBJECT_NAME(@@procid)) = 1 RETURN
-- trigger code goes here
Then
The trigger still exists but will be bypassed in this session, but will fire in other sessions
DECLARE
@vTrigger varbinary(128)
SET @vTrigger = CAST('NonUniqueITIN' as varbinary(128))
SET CONTEXT_INFO @vTrigger
/** Update a table for both clients **/
UPDATE PersonTable SET personType=1
SET @vTrigger = CAST('' as varbinary(128))
SET CONTEXT_INFO @vTrigger
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.] -
Here is the solution I developed with everyones recommendations.
1) The Disable works great (slight format change - I'm not sure if it is because I'm using SQL 2000). I had to disable several triggers before performing an update.
2) I also dynamically created a new Trigger (wanted to revise the original Trigger and did it all in the same script). Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NonUniqueITIN]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
/*** Rebuilding trigger in a new methodology ***/
drop trigger [dbo].[NonUniqueITIN]
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BizEntityAdminChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
ALTER TABLE [PersonTable] DISABLE TRIGGER [BizEntityAdminChange]
END
GO
/** Update a table for both clients **/
UPDATE PersonTable SET personType=1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BizEntityAdminChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
ALTER TABLE [] ENABLE TRIGGER [BizEntityAdminChange]
END
GO
if (exists (select * from [dbo].TblProgramOwner WHERE owner='A'))
BEGIN
DECLARE @sSetTrigger varchar(2000),
@sCRLF char(2),
@STAB char(1)
SET @sCRLF = char(13) + char(10)
SET @STAB = char(9)
SET @sSetTrigger = ''
SET @sSetTrigger = @sSetTrigger + 'CREATE TRIGGER [dbo].[NonUniqueITIN] ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + 'ON [dbo].[PersonTable] ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + 'FOR UPDATE, INSERT' + @sCRLF
SET @sSetTrigger = @sSetTrigger + 'As ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + 'IF EXISTS' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + '(' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB +'SELECT DISTINCT' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB +'i.*' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + 'FROM' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + 'Inserted i' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + 'INNER JOIN [2006_TblPartner] tbl' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + @STAB + 'on i.F030 = tbl.F030' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + 'WHERE' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + 'i.Id tbl.Id ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + ')' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB +'BEGIN' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + 'RAISERROR(' + '''' + 'Non-unique ITIN' + '''' + ', 16, 1) ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB + @STAB + 'ROLLBACK TRAN ' + @sCRLF
SET @sSetTrigger = @sSetTrigger + @STAB + @STAB +'END'
PRINT @sSetTrigger
EXEC (@sSetTrigger)
END
-
I just have one question.
Do you disable tables and columns you don't use in an application or do you drop them, or even better, don't install them at all in production?
-
We conditionally bypass triggers for some batch processes on the production database. We don't bypass triggers in the application.
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.] -
I was reffering to the create / disable version. If you're going to write that logic, you might as well DROP IT. That way there can't be any confusion later on as to know that trigger is not enabled.
-
Not the best approach I've ever seen.
Actually, one of the worst.
Why not just include the check into the trigger itself?
Create Trigger .. blah-blah-blah
AS
BEGIN
IF dbo.IsOwner(suser_sname()) = 0
RETURN
[Trigger body]
END
Of course, users must connect to the server using proper accounts, not as "sa".
But if you insist on parameter from application then move the code out of trigger. It does not belong there.
Trigger must depend only on data in "inserted" and "deleted" tables and data in static tables in database.
_____________
Code for TallyGenerator -
Why keep a do nothing trigger in the database?
Interesting how opinions can be made without knowing the business rules and logic of the triggers, users, etc.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply