Update trigger fails on count condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update trigger fails on count condition

Author

Topic

Filip Top
Starting Member

3 Posts

Posted - 2012-02-07 : 06:04:32

I use a after trigger to make a textfile with the data that has been inserted.
Because this is only allowed once I use following condition:

IF (SELECT COUNT(*) FROM INSERTED WHERE QWreleaseStatus = 'Released' AND RohmBooked = 0) = 1
BEGIN
-- Make the textfile
--Update the rohmbooked bit
UPDATE
dbo.TruckOrder --this is the table where the trigger is on
SET
RohmBooked = 1
WHERE
TruckOrderID = @ID
END

Now I noticed that the textfile is created more than once, so I set some debugging on it
At the start of the trigger I set an insert query in a logging table

INSERT INTO
dbo.LogRohmbit(ASNnummer, ReleaseStatus, [bit])
SELECT ASNnumber, QWreleaseStatus, RohmBooked FROM INSERTED

The strange thing is that i see that the RohmBooked writes to the table as a 1, so the condition is not true, but still it goes into the if condition.
Are triggers not realible to do things like this, or are I'm missing something??

Any help or idees would be very greet.
Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 06:58:02

Does your insert statement insert more than one row in a single insert statement? If so, that could be one explanation.

Filip Top
Starting Member

3 Posts

Posted - 2012-02-07 : 10:08:29

The insert or updates happens through a stored procedure, and as far as I know it get triggerd for every update/insert.

So the can happen quickly after each other, but i would be supprised if that can cause problems.

In every case, I putted the ID in both the log tables, to see if it changes through the trigger.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 10:38:54

What I meant was something slightly different - see the example below:

CREATE TABLE #tmp1 (id INT);
CREATE TABLE #tmp2 (id INT);

INSERT INTO #tmp1 VALUES (1);
INSERT INTO #tmp1 VALUES (2);

-- in the next statement, if there was a trigger on #tmp2,
-- that trigger will be fired once, but two rows will get inserted.
-- so the INSERTED virtual table would have two rows.
INSERT INTO #tmp2 SELECT id FROM #tmp1;

DROP TABLE #tmp1;
DROP TABLE #tmp2;

Filip Top
Starting Member

3 Posts

Posted - 2012-02-07 : 11:18:49

Hey Sunitabeck,

Now I understand how you can insert in once multiple rows, didn't think that far :-(
But this is not the case, every insert happens by an application that inserts/updates one by one.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 12:38:49

Can you post the complete code for your trigger? Specifically, what I am trying to find is whether it is a trigger only for INSERT, or whether it is for UPDATE and INSERT. If it is for UPDATE and INSERT, it is possible that the trigger gets fired recursively.

Kristen
Test

22859 Posts

Posted - 2012-02-07 : 13:36:24

"But this is not the case, every insert happens by an application that inserts/updates one by one."

If that is the case you MUST enforce this in the trigger, otherwise you will be in trouble in the future.


IF (SELECT COUNT(*) FROM INSERTED <> 1)
BEGIN
    RAISERROR(...
    ROLLBACK ...
END

but FWIW we do allow this logic under any circumstances here. Sooner or later someone will need to do:


UPDATE U
SET MyColumn = @SomeValueThatFixesAProblem
FROM MyTable AS U


INSERT INTO MyTable
SELECT *
FROM MyStagingTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources