Trigger not executing the first time.

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 2000 Forums

Transact-SQL (2000)

Trigger not executing the first time.

Author

Topic

bothapn
Starting Member

5 Posts

Posted - 2006-09-21 : 03:03:39

Hi,

I'm kinda new to the MS Sql thing and my first experience with has just had to begin with debugging a trigger. So any help will be appreciated.
It is a trigger on a table that gets triggered on UPDATE statements. When you first post an update query to the table, the trigger doesnt run fully(to the end), but when you execute it again, it runs fine.
Any ideas? I can post the script if needed.
Where can I begin to debug the script?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 03:38:46

Please post the trigger

bothapn
Starting Member

5 Posts

Posted - 2006-09-21 : 03:41:06

CREATE TRIGGER [dbo].[D03D09] ON [dbo].[_rtblIncidents]
AFTER UPDATE, INSERT
AS
declare
@incidentID varchar(50)
set @incidentID = (Select idIncidents from inserted)
IF UPDATE (ulINCApproved)
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="3 Gold"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="4 Platinum"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D07 Staking"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D08 Pers. Vermindering"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D10"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D03 Arbeidshof/CCMA"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCTCost/2
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D03 Arbeidshof/CCMA"
END

BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost/2
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D04 Konsultasie"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost/2
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D05 Unie Onderhandeling"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D06 Dissiplinêr"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt+dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="2 Silver" and dbo._rtblIncidents.ulINCDiens="D09 Dokumentasie"
END

BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D07 Staking"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D08 Pers. Vermindering"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCTCost
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D03 Arbeidshof/CCMA"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D04 Konsultasie"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D05 Unie Onderhandeling"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D06 Dissiplinêr"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D09 Dokumentasie"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="1 Bronze" and dbo._rtblIncidents.ulINCDiens="D10"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=0
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="3 Gold"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=0
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="4 Platinum"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D07 Staking"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D08 Pers. Vermindering"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D03 Arbeidshof/CCMA"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D04 Konsultasie"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D05 Unie Onderhandeling"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D06 Dissiplinêr"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D09 Dokumentasie"
END
BEGIN
UPDATE dbo._rtblIncidents
SET DUnAppr=dbo._rtblIncidents.ufINCSAmt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCCat="5 Copper" and dbo._rtblIncidents.ulINCDiens="D10"
END

IF UPDATE (ulINCApproved)
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCAAmt=dbo._rtblIncidents.Dappr+dbo._rtblIncidents.ufincoamt
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCApproved="Yes"
END
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCDAmt=dbo._rtblIncidents.DUnAppr
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCApproved="Yes"
END
IF UPDATE (ulINCApproved)
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCAAmt=0
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCApproved="No"
END
BEGIN
UPDATE dbo._rtblIncidents
SET ufINCDAmt=ufINCCAmt-ufINCspecappr
where idIncidents = @Incidentid and dbo._rtblIncidents.ulINCApproved="No"
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 03:56:32

quote:
set @incidentID = (Select idIncidents from inserted)

You are assuming that there is only one record get inserted / updated at one time. Which is a common misconception on trigger. Trigger will execute when records in a table is inserted or updated. Which maybe more than one records. The inserted table will contains the inserted or updated records.

You will need to re-write your trigger to allow for this scenario.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 03:58:38

here are some reference on trigger
http://www.sqlteam.com/item.asp?ItemID=3850
http://www.sql-server-performance.com/nn_triggers.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 04:01:52

Also, the check for IF UPDATE (ulINCApproved) only applies to first update statement.
All other updates are always run for all inserts and all updates.

Peter Larsson
Helsingborg, Sweden

bothapn
Starting Member

5 Posts

Posted - 2006-09-21 : 04:44:16

Wow thanks for the help guys.
Peso, what do you mean with : "All other updates are always run for all inserts and all updates." ?

Thanx!

bothapn
Starting Member

5 Posts

Posted - 2006-09-21 : 04:55:56

Ok, I understand the IF UPDATE thing.. rewrite time.. (btw.. I did not write that trigger)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 05:13:17

Fewer lines and easier to read. BUT.. Since the logic behind your IF's are not known, I have omitted them.

CREATE TRIGGER [dbo].[D03D09] ON [dbo].[_rtblIncidents] 
AFTER UPDATE, INSERT
AS

UPDATE	dbo._rtblIncidents
SET	DAppr = dbo._rtblIncidents.ufINCSAmt + dbo._rtblIncidents.ufINCTCost,
	DUnAppr = 0
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat IN ('3 Gold', '4 Platinum')

UPDATE	dbo._rtblIncidents
SET	DAppr = dbo._rtblIncidents.ufINCSAmt + dbo._rtblIncidents.ufINCTCost
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '2 Silver'
	AND dbo._rtblIncidents.ulINCDiens IN ('D07 Staking', 'D08 Pers. Vermindering', 'D10'. 'D06 Dissiplinêr', 'D09 Dokumentasie')

UPDATE	dbo._rtblIncidents
SET	DAppr = dbo._rtblIncidents.ufINCSAmt
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '1 Bronze'
	AND dbo._rtblIncidents.ulINCDiens IN ('D03 Arbeidshof/CCMA', 'D07 Staking', 'D08 Pers. Vermindering', 'D04 Konsultasie', 'D05 Unie Onderhandeling', 'D06 Dissiplinêr', 'D09 Dokumentasie', 'D10')

UPDATE dbo._rtblIncidents
SET	DUnAppr = dbo._rtblIncidents.ufINCTCost / 2
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '2 Silver'
	AND dbo._rtblIncidents.ulINCDiens IN ('D03 Arbeidshof/CCMA')

UPDATE	dbo._rtblIncidents
SET	DUnAppr = dbo._rtblIncidents.ufINCSAmt + dbo._rtblIncidents.ufINCTCost / 2
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '2 Silver'
	AND dbo._rtblIncidents.ulINCDiens IN ('D04 Konsultasie', 'D05 Unie Onderhandeling')

UPDATE	dbo._rtblIncidents
SET	DUnAppr = dbo._rtblIncidents.ufINCTCost
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '1 Bronze'
	AND dbo._rtblIncidents.ulINCDiens IN ('D03 Arbeidshof/CCMA')

UPDATE	dbo._rtblIncidents
SET	DUnAppr = dbo._rtblIncidents.ufINCSAmt
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCCat = '5 Copper'
	AND dbo._rtblIncidents.ulINCDiens IN ('D07 Staking', 'D08 Pers. Vermindering', 'D03 Arbeidshof/CCMA', 'D04 Konsultasie', 'D05 Unie Onderhandeling', 'D06 Dissiplinêr', 'D09 Dokumentasie', 'D10')

UPDATE	dbo._rtblIncidents
SET	ufINCAAmt = dbo._rtblIncidents.Dappr + dbo._rtblIncidents.ufincoamt,
	ufINCDAmt = dbo._rtblIncidents.DUnAppr
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCApproved = 'Yes'

UPDATE	dbo._rtblIncidents
SET	ufINCAAmt = 0,
	ufINCDAmt = ufINCCAmt-ufINCspecappr
WHERE	idIncidents IN (SELECT idIncidents FROM inserted)
	AND dbo._rtblIncidents.ulINCApproved = 'No'

Peter Larsson
Helsingborg, Sweden

bothapn
Starting Member

5 Posts

Posted - 2006-09-21 : 05:39:40

WOW O_O. THANX!!!
I now just how to test it but I think it will work. I also dont know what the logic behind the IF's are.
Thanks again for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources