Table EXA is as follows: Create Table Exa (ID Int Id Idness (1, 1) Not Null, Status Bit Null, Primary Key (ID)) Select * from Exaid Status --------------- - 4 16 1
Table EXB is as follows: Create Table Exb (ID Int Idness "Not Null, AID INT NULL, MARK VARCHAR (50) NULL, PRIMARY Key (ID), Foreign Key (AID) References Exa On Delete Cascade ON Update Cascade Select * from exbid Aid Mark -------------------------------------------------------------------------------------------------------------------- ---------------------------- 76 4 TEST177 4 TEST278 4 TEST379 4 TEST484 6 TEST185 6 Test286 6 Test387 6 Test4
EXA.ID and Exb.AID are a pair of relationships.
Now, when the recording is deleted in the EXB, check the table, if the data of the EXA does not find the corresponding data in the EXB, set the exa.status to 0.
Trigger follows: Alter Trigger trTestExOn ExBFor DeleteAsDeclare curDelete cursor for select Aid from Deleted group by AidDeclare @intAid intOpen curDeleteFetch Next from curDelete Into @intAidwhile @@ Fetch_Status = 0Begin if exists (select * from ExA where id = @ intAid) Begin if not EXISTS (Select * from exb where aid = @ INTAID) Update exa set status = 0 Where id = @ Intaid End Fetch next from curdelete @intaidendclose CurdeleteDeallocate Curdeletego
