i am trying to develop an afterupdate trigger of my table PROJECTREQUESTEDCHANge_Test that will runs different bits of code depending on the bit fields that are changed, below are the test tables i have set up and also the trigger that i have written. the main problems are

1. getting a field count from the other tables
i've used IF (SELECT FIELDTHREE FROM INSERTED) <> 0, but i ma not sure if this is the best way to do it

2. I seem to have too many begin and ends

I would be grateful if someone could have a look at my trigger and maybe point me in the right direction.

many thanks


TableName : PROJECTREQUESTEDCHANge_Test
PK FIELD ProjectID, bigint
fieldOne bit
fieldTwo bit
fieldThree bit
fieldfour bit
fieldfive bit
TableName : TABLE_B
PK FIELD ProjectID, bigint

TableName : TABLE_C
PK FIELD ProjectID, bigint

Create Trigger U_GenerateRequestedChange
on PROJECTREQUESTEDCHANge_Test
for UPDATE
AS
DECLARE @PROJECTID BIGINT
@PROJECTID = SELECT PROJECTid from inserted

--check this field
IF UPDATE(FIELDONE)
BEGIN
IF (SELECT FIELDONE FROM INSERTED) <> 0 --fieldone is a bit datatype, so if its not equal to 0 then it must 1 '
BEGIN
--if fieldone = 1 then, then check table_b to see if there are any records in table_b with a projectID that is the same as a field in inserted called requestedchangeID
IF (Select Count(*) FROM TABLE_B WHERE PROJECTID = @PROJECTID) <= 0
--if there isn't any records int table_B, then run the code
BEGIN
--RUN CODE
END
END
--check this field
IF UPDATE(FIELDTWO)
BEGIN
IF (SELECT FIELDTWO FROM INSERTED) <> 0 --fieldtwo is a bit datatype, so if its not equal to 0 then it must 1 '
BEGIN
--if fieldone = 1 then, then check table_b to see if there are any records in table_b with a projectID that is the same as a field in inserted called requestedchangeID
IF (Select Count(*) FROM TABLE_C WHERE PROJECTID = @PROJECTID) <= 0
--if there isn't any records in TABLE_C, then run the code
BEGIN
--RUN CODE
END
END

--check this field
IF UPDATED(FIELDTHREE)
BEGIN
IF (SELECT FIELDTHREE FROM INSERTED) <> 0 --FIELDTHREE is a bit datatype, so if its not equal to 0 then it must 1 '
BEGIN
IF (SELECT FIELDFOUR FROM INSERTED) <> 0 -- fieldfour is also a bit datatype
--if fieldthree and fieldfour are both 1
BEGIN
--RUN THE CODE TO UPDATE THE CORRESPONDING REQUESTED CHAGNE RECORD
END
IF (SELECT FIELDFIVE FROM INSERTED) <> 0 --fieldfive is also a bitdatatype
--if fieldthree and fieldfive
BEGIN
--RUN THE CODE TO UPDATE THE CORRESPONDING REQUESTED CHAGNE RECORD
END
END
END

/

I don't think I understood that. Looking at the SQL code, I wonder what does FIELD count mean there?

You should be looking at indentation (tabbing the lines accordingly) of code to avoid counting how many BEGINs and ENDs are actually there and how many match.



That should pretty much solve the main problems

Hope that helps.

Cheers,

__________________
- Vijay G