I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.
Update Race:
IFNotUPDATE(Edited)
UPDATE Patient
SET Race =(SELECTCASE Racecode
WHEN'API'THEN'Asian, Pacific Islander'
WHEN'BH'THEN'Black, Hispanic'
WHEN'BNH'THEN'Black, Non-Hispanic'
WHEN'HIS'THEN'Hispanic'
WHEN'OTH'THEN'Other, Black & White'
WHEN'WH'THEN'White, Hispanic'
WHEN'WNH'THEN'White, Non-Hispanic'
END)
WHERE id# IN(SELECT id# FROM inserted)
END
Update Religion:
IF Not UPDATE (Edited)
Update Patient
SET Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM Inserted)
END
why not combining them into one update.IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!
