cancel
Showing results for 
Search instead for 
Did you mean: 

Error in Trigger

tgyc001
Explorer
0 Kudos

Hello community,
This request is a trigger which was created on the [@SUIVI_COLIS] table. The trigger is fired when there is an update on the table.

In this case, the trigger checks if certain fields (U_Date, U_Quart, U_Colis, U_PO, U_PRI, U_VM, U_Grade, U_PB, U_N_Odre_Prod, U_Lot, U_ML, U_Dirt, U_Ash, U_Nitro, U_Grade_F and U_CHEQ) have been updated and if the status is "C". If these conditions are met, an error is returned and the transaction is rolled back, meaning the changes will not be made.

This is a security measure to prevent certain fields from being modified when the status is "C". Does this answer your question

But it doesn't turn out as expected.
it is triggered each time an update is carried out regardless of the status of the line.

Please help me resolve this issue.

ALTER TRIGGER [dbo].[BloquerModification_SUIVI_COLIS]
ON [dbo].[@SUIVI_COLIS]
FOR UPDATE
AS
BEGIN
    IF UPDATE(U_Date) OR UPDATE(U_Quart) OR UPDATE(U_Colis) OR UPDATE(U_PO) OR UPDATE(U_PRI) 
   OR UPDATE(U_VM) OR UPDATE(U_Grade) OR UPDATE(U_PB) OR UPDATE(U_N_Odre_Prod) OR UPDATE(U_Lot) 
   OR UPDATE(U_ML) OR UPDATE(U_Dirt) OR UPDATE(U_Ash) OR UPDATE(U_Nitro) OR UPDATE(U_Grade_F) OR UPDATE(U_CHEQ) 
   AND EXISTS  (SELECT 1 FROM inserted WHERE Status = 'C' AND [DocEntry] = inserted.[DocEntry])
    BEGIN
        RAISERROR('La modification des champs U_Date, U_Quart, U_Colis, U_PO, U_PRI, U_VM, U_Grade, U_PB, U_N_Odre_Prod, U_Lot, U_ML, U_Dirt, U_Ash, U_Nitro, U_Grade_F et U_CHEQ est interdite lorsque le statut est "C".', 16, 1)
        ROLLBACK TRANSACTION
    END
END
Johan_H
Active Contributor
0 Kudos
Why do you not use the 'SBO_SP_TransactionNotification' stored procedure for this?
View Entire Topic
tgyc001
Explorer
0 Kudos

I used "SBO_SP_TransactionNotification" before performing the trigger. But the table is a User Table of type "document" so it is difficult to find the objecType.

Johan_H
Active Contributor
0 Kudos
@ANKIT_CHAUHAN, can you help? How can we determine the objectType of a user defined table?
felipeprevente
Explorer

Hi @tgyc001 ,

Considering your table is registered as a UDO, you have to reference it within SBO_SP_TransactionNotification between quotes. See below:

felipeteodoro_0-1709122077169.png

felipeteodoro_1-1709122181724.png

Regards,

Felipe