#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    39
    Rep Power
    8

    Trigger Work on Insert but not Work on Delete


    hi all, i am really confused whats wrong,cause the syntax is same.but on insert it works on Delete it not works

    this is insert trigger when inserting record
    Code:
    DECLARE VARIABLE JENIS VARCHAR(20); 
    DECLARE VARIABLE IDCAB INTEGER ; 
    BEGIN 
    SELECT TIPE FROM MASTERPERKIRAAN WHERE KODE=NEW.KODEAKUN INTO JENIS; 
    SELECT IDCABANG FROM MASTERJURNAL WHERE ID=NEW.IDMASTERJURNAL INTO :IDCAB; 
    /* Trigger text */ 
    IF (JENIS='D') 
    THEN 
    UPDATE MASTERPERKIRAAN SET MASTERPERKIRAAN.TOTALSALDO=
    MASTERPERKIRAAN.TOTALSALDO+(NEW.DEBIT-NEW.KREDIT) WHERE MASTERPERKIRAAN.IDCABANG=:IDCAB AND NEW.KODEAKUN=MASTERPERKIRAAN.KODE OR (SUBSTRING(NEW.KODEAKUN FROM 1 FOR 2)= SUBSTRING(MASTERPERKIRAAN.KODE FROM 1 FOR 2) AND MASTERPERKIRAAN.TINGKAT=0) ;
    ELSE 
    UPDATE MASTERPERKIRAAN SET MASTERPERKIRAAN.TOTALSALDO=
    MASTERPERKIRAAN.TOTALSALDO+(NEW.KREDIT-NEW.DEBIT) 
    WHERE MASTERPERKIRAAN.IDCABANG=:IDCAB AND NEW.KODEAKUN=MASTERPERKIRAAN.KODE OR (SUBSTRING(NEW.KODEAKUN FROM 1 FOR 2)= SUBSTRING(MASTERPERKIRAAN.KODE FROM 1 FOR 2) AND MASTERPERKIRAAN.TINGKAT=0) ;
    END
    and this is syntax trigger when deleting record:
    Code:
    DECLARE VARIABLE JENIS VARCHAR(20); 
    DECLARE VARIABLE IDCAB INTEGER ; 
    BEGIN 
    SELECT TIPE FROM MASTERPERKIRAAN WHERE KODE=NEW.KODEAKUN INTO JENIS; 
    SELECT IDCABANG FROM MASTERJURNAL WHERE ID=NEW.IDMASTERJURNAL INTO :IDCAB;
     /* Trigger text */ 
    IF (JENIS='D') 
    THEN 
    UPDATE MASTERPERKIRAAN SET MASTERPERKIRAAN.TOTALSALDO=MASTERPERKIRAAN.TOTALSALDO
    +(NEW.DEBIT-NEW.KREDIT) WHERE MASTERPERKIRAAN.IDCABANG=:IDCAB AND NEW.KODEAKUN=MASTERPERKIRAAN.KODE OR (SUBSTRING(NEW.KODEAKUN FROM 1 FOR 2)= SUBSTRING(MASTERPERKIRAAN.KODE FROM 1 FOR 2) AND MASTERPERKIRAAN.TINGKAT=0) ;
    ELSE
    UPDATE MASTERPERKIRAAN SET MASTERPERKIRAAN.TOTALSALDO=MASTERPERKIRAAN.TOTALSALDO+
    (NEW.KREDIT-NEW.DEBIT) WHERE MASTERPERKIRAAN.IDCABANG=:IDCAB AND NEW.KODEAKUN=MASTERPERKIRAAN.KODE OR (SUBSTRING(NEW.KODEAKUN FROM 1 FOR 2)= SUBSTRING(MASTERPERKIRAAN.KODE FROM 1 FOR 2) AND MASTERPERKIRAAN.TINGKAT=0) ;
    END
    the problem is when deleting the IDCAB not have value,cause when i use 0 replace the IDCAB,it works.
    Last edited by rusdyrip; March 31st, 2013 at 07:23 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    There is no NEW for Delete.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0
    You should be using OLD. instead of NEW. for the delete.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Firebird allows using NEW in DELETE triggers, but the values are NULL As there is OLD in INSERT triggers but again with NULL values. Only in UPDATE you have data in OLD and NEW.
    Here is a little article about triggers in Firebird Firebird Triggers
    Last edited by mIRCata; May 13th, 2014 at 01:36 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    If you're using Firebird 1.5 and up, and you have a trigger that fires on both INSERT and DELETE, which is possible, you can
    also check the pseudo variable DELETING/INSERTING or UPDATING to see which is happening.
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com

IMN logo majestic logo threadwatch logo seochat tools logo