I have a requirement of which I want to create a trigger in Oracle.

So what I want is: I have a table called XXCUS.XXACL_PN_PROSPECT_TRL in which there is a column called ACTION whose default value is set as RELEASE, EXTEND and CANCEL.

So my trigger would be on the table XXCUS.XXACL_PN_PROSPECT_TRL as

whenever the column ACTION has values as

  1. RELEASE then the other table column should get UPDATED as R with the same matching ID

  2. EXTEND then the other table column should get UPDATED as O with the same matching ID

  3. CANCEL then the other table column should get UPDATED as C with the same matching ID

The other table name is xxcus.xxacl_pn_leases_all whose column needs to be updated. Also the column name is CLOSE_FLAG.

I m trying like below

CREATE [ OR REPLACE ] TRIGGER XXACL_PN_PROSPECT_T
BEFORE INSERT
  ON XXCUS.XXACL_PN_PROSPECT_TRL
  [FOR EACH ROW]
 DECLARE
   -- variable declaration
 BEGIN
   -- trigger code
  Insert into XXCUS.XXACL_PN_PROSPECT_TRL -- values for ACTION here
   then 
  update xxcus.xxacl_pn_leases_all if 'RELEASE' then 'R', if 'EXTEND' then  'O' where mkey = dynamic
 EXCEPTION
   WHEN ...
  -- exception handling
  END;

but it is not working as I am not champ in creating Triggers. Kindly help me with this

UPDATE

Finally I created a trigger:-

CREATE OR REPLACE TRIGGER xxcus.xxacl_pn_prospect_trl_trg
  AFTER INSERT OR UPDATE
  ON xxcus.xxacl_pn_prospect_trl
  FOR EACH ROW
 DECLARE
  sql_error   VARCHAR (10000);
  v_mkey      NUMBER;
  v_action    VARCHAR (10);
BEGIN
  v_action := :NEW.action;
  IF (v_action = 'Extend')
 THEN
    UPDATE xxcus.xxacl_pn_leases_all
     SET no_of_days = :NEW.current_action_days
   WHERE lease_num = :NEW.lease_no AND mkey = :NEW.mkey;
    ELSIF (v_action = 'Release')
     THEN
      UPDATE xxcus.xxacl_pn_leases_all
      SET no_of_days = 0,
         close_flag = 'R'
     WHERE lease_num = :NEW.lease_no AND mkey = :NEW.mkey;
    ELSE
      UPDATE xxcus.xxacl_pn_leases_all
       SET no_of_days = 0,
          close_flag = 'C'
     WHERE lease_num = :NEW.lease_no AND mkey = :NEW.mkey;
     END IF;
    EXCEPTION
   WHEN OTHERS
  THEN
    sql_error := SQLERRM;
  END;

Related posts

Recent Viewed