September 21, 2006 at 12:53 am
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.
September 21, 2006 at 1:12 am
Yes please, post the trigger code and table structure. It will be a lot easier to look for solution and explain problems if we all know what it is about.
What does it mean "doesn't run to the end"? Is it rolled back, or it performs some actions and update occurs, but some of actions in the trigger don't happen when you expect them to do so?
September 21, 2006 at 1:37 am
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
September 21, 2006 at 2:13 am
OK, to start with, how do you expect this will work if multiple rows are updated? You are trying to assign multiple values to one variable.
declare
@incidentID varchar(50)
set @incidentID = (Select idIncidents from inserted)
You should JOIN to inserted table in a trigger to work with multiple values.
Another thing - you have one IF followed by a bunch of BEGIN..END blocks. Only first block is affected by IF, other blocks execute always. They have no meaning at all if not preceded by some condition. You should enclose all the statements following IF UPDATE() into one BEGIN..END.
And last thing... I'm absolutely at a loss what the trigger should do and why you have so many statements with different conditions, but always the same things in SET. Why is that not one update with multiple conditions (using OR or IN etc.), if the SET clause is identical?
September 21, 2006 at 2:38 am
Something like this should be closer to the correct result, but I still have no idea what the trigger should do, as it is not apparent for your code - you may have been trying to do something entirely different. Can you describe in words what the trigger should do? Here it means that any time column ulINCApproved is updated, you add or subtract something from a column.. this may give you rather unexpected results. Again, please post explanation of what should happen.
CREATE TRIGGER [dbo].[D03D09] ON [dbo].[_rtblIncidents]
AFTER UPDATE, INSERT
AS
IF UPDATE (ulINCApproved)
BEGIN
UPDATE inc
SET DAppr=inc.ufINCSAmt+inc.ufINCTCost
FROM dbo._rtblIncidents inc
JOIN inserted i ON i.idIncidents = inc.idIncidents
where inc.ulINCCat='3 Gold'
OR inc.ulINCCat='4 Platinum'
OR (inc.ulINCCat='2 Silver' and inc.ulINCDiens IN ('D07 Staking', 'D08 Pers. Vermindering','D10'))
UPDATE dbo._rtblIncidents
SET DAppr=inc.ufINCSAmt
FROM dbo._rtblIncidents inc
JOIN inserted i ON i.idIncidents = inc.idIncidents
where inc.ulINCCat='1 Bronze' and inc.ulINCDiens='D03 Arbeidshof/CCMA'
UPDATE inc
SET DUnAppr=inc.ufINCTCost/2
FROM dbo._rtblIncidents inc
JOIN inserted i ON i.idIncidents = inc.idIncidents
where inc.ulINCCat='2 Silver' and inc.ulINCDiens='D03 Arbeidshof/CCMA'
UPDATE inc
SET DUnAppr=inc.ufINCSAmt+inc.ufINCTCost/2
FROM dbo._rtblIncidents inc
JOIN inserted i ON i.idIncidents = inc.idIncidents
where inc.ulINCCat='2 Silver' and (inc.ulINCDiens='D04 Konsultasie' OR inc.ulINCDiens='D05 Unie Onderhandeling')
......
END
September 21, 2006 at 2:50 am
Thanks for the help. I did not write the trigger (luckily). I'm the lucky guy who got the job to debug/fix it :/
Though, I really appreaciate your help. I hope I can fix/rewrite the poor script.
Thanks!
Oh,I have not been told what the script should do just to make it work.
September 21, 2006 at 3:06 am
Oh... then the only thing you can do is to find out why it is there, what it should do and so on... otherwise you're stuck. You can not rewrite a code that's full of elementary errors, and uncommented. What does "make it work" mean? They just don't want to get error message? Delete the trigger! They want it to do something? Fine, make them tell you what.
BTW: Do you mean to say this trigger worked for some time?
September 21, 2006 at 3:15 am
Apparently the first part of the trigger works most of the time. The second (last) part of it doesnt work always. So, no syntax errors, just logic errors.
They have shown me that it does not populate some textboxes in their crm application.. but how that texboxes link to this sql table is still something I have to find out.
September 21, 2006 at 4:58 am
Populate textboxes? That's really strange, because it looks more like cost calculation... Are you sure this is the right trigger?
This trigger can do something, if rows are updated one by one. However, I very much doubt it does what it is expected to do.
September 21, 2006 at 5:07 am
In the application they enter some values and then this trigger calculate some stuff en update a few things. That's to the best of my knowledge.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply