Trigger

Discussion in 'Databases' started by chakradhar99, Aug 19, 2008.

  1. #1
    Can someone please help me in writing trigger for the below :

    TRIG_STED_AIU trigger on table STATE_TAX_EXEMPT_DEFAULT
    after insert or update of columns STED_RES_TAX_EXEMPT_PCN, STED_IND_COMM_TAX_EXEMPT_PCN do the following:

    Update all BUSPART
    Set BUS_RES_TAX = 100 - STED_RES_TAX_EXEMPT_PCN
    BUS_IND_COMM_TAX= 100 - STED_IND_COMM_TAX_EXEMPT_PCN
    where buspart_state = sted_state_cd
    and (If sted_item_type_id = 1 and BUSPART.util_type_code = ‘G’ (gas)
    or
    sted_item_type_id = 2 and BUSPART.util_type_code = ‘E’ (electric) )
    and buspart_reltyp ‘LD’ (utility)

    Note: there may be no existing business partners to update, so do not create an error in this circumstance.
     
    chakradhar99, Aug 19, 2008 IP
  2. chakradhar99

    chakradhar99 Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Wrote the following :

    CREATE OR REPLACE TRIGGER RSSC.TRIG_STED_AIU

    AFTER INSERT OR UPDATE OF sted_res_tax_exempt_pcn,sted_ind_comm_tax_exempt_pcn
    ON RSSC.STATE_TAX_EXEMPT_DEFAULT
    --REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
    WHEN (BUSPART.buspart_state = STATE_TAX_EXEMPT_DEFAULT.sted_state_cd
    AND
    (STATE_TAX_EXEMPT_DEFAULT.sted_item_type_id = 1 AND BUSPART.utl_type_code = 'G'
    OR
    STATE_TAX_EXEMPT_DEFAULT.sted_item_type_id = 2 AND BUSPART.utl_type_code = 'E')
    AND
    BUSPART.buspart_reltyp = 'LD')

    BEGIN

    UPDATE ALL BUSPART
    SET BUSPART.bus_res_tax = 100 - STATE_TAX_EXEMPT_DEFAULT.sted_res_tax_exempt_pcn,
    SET BUSPART.bus_ind_comm_tax = 100 - STATE_TAX_EXEMPT_DEFAULT.sted_ind_comm_tax_exempt_pcn

    END TRIG_STED_AIU;

    Getting the following error :
    ORA-04076: invalid NEW or OLD specification
     
    chakradhar99, Aug 19, 2008 IP
  3. chakradhar99

    chakradhar99 Peon

    Messages:
    5
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Can someone please help me
     
    chakradhar99, Aug 19, 2008 IP