#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
    12
    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.

IMN logo majestic logo threadwatch logo seochat tools logo