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

    Join Date
    Jun 2007
    Posts
    38
    Rep Power
    7

    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,055
    Rep Power
    383
    There is no NEW for Delete.

IMN logo majestic logo threadwatch logo seochat tools logo