August 22, 2001 at 11:21 am
I have two triggers on a table, one for ON INSERT and one for ON UPDATE. The two triggers are designed to add a line of history to a field when certain operations are peformed. ON INSERT adds a line "created on etc etc" and the UPDATE checks via IF UPDATE() on a single column and adds a similar line to the history field "Record changed from etc etc".
Both triggers seem to work just fine independently, but when they are both activated, the history field remains null. Obviously, they are conflicting somehow, but it doesn't seem to make any sense to me.
Any ideas? I can post the code if necessary...
Thanks,
Shawn Cheris
August 22, 2001 at 1:48 pm
They shouldn't be conflicting, but one never knows. Yes, please post the code for both triggers.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
August 22, 2001 at 2:23 pm
August 22, 2001 at 2:45 pm
Thanks for the replies. Keep in mind that I am *very* new to this so any additional comments you might have on my code would be appreciated.
trigger 1...
CREATE TRIGGER [hist1] ON dbo.btbatch
FOR INSERT
AS
declare @timecreate as nvarchar(20)
declare @datecreateas nvarchar(20)
declare @hist as varchar(200)
--convert date into string / build history line
set @timecreate = CONVERT(char(20), (getdate()), 8)
set @timecreate = ltrim(rtrim(@timecreate))
set @datecreate = CONVERT(char(20), (getdate()), 1)
set @datecreate = ltrim(rtrim(@datecreate))
set @hist = ('Batch created on ' + @datecreate + ' at ' + @timecreate)
--insert statement into instory
update btbatch
set btbhist = @hist
where btbatch.btbbcn = (select btbbcn from inserted)
--insert date into btbin to show when batch was first put into system
update btbatch
set btbin = getdate()
where btbatch.btbbcn = (select btbbcn from inserted)
--insert date into btblastout to show the time of the last operation performed
update btbatch
set btblastout = getdate()
where btbatch.btbbcn = (select btbbcn from inserted)
--insert record into btprint to queue it for label printing
insert into btprint (btpbcn, btpclnt, btpstate, btppg, btpsorter, btppri)
select btbbcn, btbclnt, btbstate, btbpg, btbsorter, btbpri from btbatch where btbbcn = (select btbbcn from inserted)
trigger 2...
CREATE TRIGGER [history_update2] ON dbo.btbatch
FOR UPDATE
AS
declare @oldclntas varchar(50)
declare @newclnt as varchar(50)
declare @newhistas nvarchar(500)
declare @oldhist as nvarchar(4000)
declare @histas nvarchar(4000)
declare @timecreate as varchar(20)
declare @datecreateas varchar(20)
--get the date ready
set @timecreate = CONVERT(char(20), (getdate()), 8)
set @timecreate = ltrim(rtrim(@timecreate))
set @datecreate = CONVERT(char(20), (getdate()), 1)
set @datecreate = ltrim(rtrim(@datecreate))
--if the client has been changed, add this to the log
if update(btbclnt)
if not update(btbbcn)
set @oldclnt = (select btbclnt from deleted)
set @oldhist = (select btbhist from btbatch where btbbcn = (select btbbcn from deleted))
set @newclnt = (select btbclnt from inserted)
set @newhist = (@datecreate + ' at ' + @timecreate + ' Client changed from ')
set @newhist = (@newhist + @oldclnt + ' to ' + @newclnt + '<br>')
set @hist = (@newhist + @oldhist)
update btbatch
set btbhist = @hist
where btbbcn = (select btbbcn from deleted)
August 22, 2001 at 8:03 pm
I don't know if this is your problem or not but just BEWARE: every time you do an update in trigger1 you will fire the update trigger (trigger2). Is this your intention? If you put some Print statements in your triggers it may help you to debug from Query Analyzer.
Trigger1 add the line Print 'Insert Trigger Called'
Trigger2 add the line Print 'Update Trigger called'
August 22, 2001 at 8:59 pm
You are correct in your assumption that they are conflicting. The way you have it set up, the update trigger is getting executed every time you do an insert. The update trigger @oldhist value is null, and that's what is getting returned (you concatenate it to the @newhist, but it doesn't matter). You can bypass this by setting the nested triggers option to 0 through the sp_configure procedure.
You may also want to consider keeping track of the history in a totally seaparate table. This will allow for a nice and granular row-based tracking method, and alleviate some of the problems you are haveing (and may run into when you run out of room in your field). I'm also a big fan of descriptive variable and column names. It makes for much easier debugging and helps others follow and understand your process better.
Hope this helps!
Sean
August 22, 2001 at 9:09 pm
I'm bothered by the following:
quote:
if update(btbclnt)if not update(btbbcn)
set @oldclnt = (select btbclnt from deleted)
If we were to put begin...end in to show how this actually is being interpreted, we'd see the following:
if update(btbclnt)
BEGIN
if not update(btbbcn)
BEGIN
set @oldclnt = (select btbclnt from deleted)
END -- if not update(btbbcn)
END -- if update(btbclnt)
The problem that results is that if btbcnlt isn't being updated, then @oldclnt is not populated. Well, your insert trigger updates the table, but doesn't touch btbcnlt. As a result @oldclnt is NULL. And unless you've changed the default settings, that makes the @newhist NULL and @hist NULL.
The reason for this is the way SQL Server interprets addition (or concatenation) of a NULL value. Basically, you get NULL as the result if you run the following query:
SELECT 'Test' + NULL
And that explains why you're getting the NULL values in your table (@newhist concatenates @oldclnt and then @hist concatenates @hist which results in @hist being NULL). What you probably need to do is explicitly use your BEGIN...END to define statement blocks. I'm thinking that your intention is to include everything including the UPDATE within the context of your IF branches. If you don't, SQL Server only considers the next statement as part of the if decision branch. It's the same principle as say in JavaScript where we use curly braces to define a statement block:
if (userName == "George") {
/* I want the if to include more than the first line because the two to follow won't make sense without the first one, so I use { and } to define a statement block. */
var newWin = open("", "openWin", "height=500;width=300;");
newWin.document.write("Hi, George!");
newWin.document.close();
}
Hope this makes sense.
K. Brian Kelley
Edited by - bkelley on 08/22/2001 9:10:16 PM
K. Brian Kelley
@kbriankelley
August 23, 2001 at 10:05 am
Thank you all for the suggestions. I'm going to try some different things based on your ideas.
One more question... I somehow created a trigger with a name like "test_ update" with a space after the _. I can't seem to easily dump this trigger because of the space and the fact that the second word is update... any ideas?
August 23, 2001 at 10:45 am
August 23, 2001 at 4:32 pm
One other note - you can use SET CONCAT_NULL_YIELDS_NULL to change the behavior during concatenation operations. Use this option wisely and sparingly!
Andy
December 8, 2002 at 3:53 pm
INSERT and UPDATE triggers don't fire at the same time. YOu either have an INSERT or you have and UPDATE. What you might be seeing is that either of your DML statements is applying to multiple rows with a single statement. Your trigger may not be looking at all rows in the 'insert' and/or 'updated' pseudo tables and processing them like you think.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply