Home » SQL & PL/SQL » SQL & PL/SQL » Trigger help required (Oralce 10)
Trigger help required [message #376314] Tue, 16 December 2008 22:49 Go to next message
N.SasiKanth
Messages: 35
Registered: June 2003
Member
I have to write a trigger in such a way that if i insert,delte or update any record in one table
it has to update the other table with all the rows of 1st table with the second table.
I worte a trigger as below:
create or replace
TRIGGER AR_FEE_BANDS 
AFTER INSERT OR DELETE OR UPDATE
 ON FEE_BANDS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  V_USER        CHAR(8);
  V_ACTION	CHAR(6);
  V_DATE	TIMESTAMP;
  V_REC_SEQ     NUMBER;
  
 
BEGIN
  V_DATE := CURRENT_TIMESTAMP;
  IF INSERTING THEN
     V_ACTION := 'INSERT';
     V_USER   := :NEW.MOD_USER;
  ELSIF UPDATING THEN
     V_ACTION := 'UPDATE';
     V_USER := :NEW.MOD_USER;
  ELSIF DELETING THEN
     V_ACTION := 'DELETE';
     V_USER := PKG_USER_UTIL.F_GET_USER;
  
  END IF;
  

  IF INSERTING THEN
     SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD WHERE FEE_RULE=:NEW.FEE_RULE AND AUDIT_ACTION='INSERT';
     IF V_REC_SEQ IS NULL THEN
        V_REC_SEQ:=0;
        INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
        VALUES(V_DATE, V_ACTION,V_USER,:NEW.FEE_BANDS_ID,:NEW.FEE_RULE,:NEW.FEE_BAND_LOWER_LIMIT,:NEW.FEE_BAND_HIGHER_LIMIT,:NEW.RATE_PERCENT,:NEW.RATE_NUMBER,:NEW.CAL_BASED_ON,V_REC_SEQ+1);    
     ELSE
        INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
        VALUES(V_DATE, V_ACTION,V_USER,:NEW.FEE_BANDS_ID,:NEW.FEE_RULE,:NEW.FEE_BAND_LOWER_LIMIT,:NEW.FEE_BAND_HIGHER_LIMIT,:NEW.RATE_PERCENT,:NEW.RATE_NUMBER,:NEW.CAL_BASED_ON,V_REC_SEQ+1);
     END IF;
  ELSIF DELETING THEN
     SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD  WHERE FEE_RULE=:OLD.FEE_RULE AND AUDIT_ACTION='DELETE';
     IF V_REC_SEQ IS NULL THEN
        V_REC_SEQ:=0;
       INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
       VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);    
     ELSE 
        INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
       VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);    
     END IF;
  ELSIF UPDATING THEN
     SELECT MAX(REC_SEQ) INTO V_REC_SEQ FROM FEE_BANDS_AUD  WHERE FEE_RULE=:OLD.FEE_RULE AND AUDIT_ACTION='UPDATE';
    
    IF V_REC_SEQ IS NULL THEN
        V_REC_SEQ:=0;
        INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
       VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);    
     ELSE 
        INSERT INTO FEE_BANDS_AUD(AUDIT_DATE,AUDIT_ACTION,AUDIT_USER,FEE_BANDS_ID,FEE_RULE,FEE_BAND_LOWER_LIMIT,FEE_BAND_HIGHER_LIMIT,RATE_PERCENT,RATE_NUMBER,CAL_BASED_ON,REC_SEQ)
       VALUES(V_DATE, V_ACTION,V_USER,:OLD.FEE_BANDS_ID,:OLD.FEE_RULE,:OLD.FEE_BAND_LOWER_LIMIT,:OLD.FEE_BAND_HIGHER_LIMIT,:OLD.RATE_PERCENT,:OLD.RATE_NUMBER,:OLD.CAL_BASED_ON,V_REC_SEQ+1);
     END IF;
    
  END IF;
  
END;


THis needs to be modified in such way that i update,insert or delte , i have update fee_bands_audit with all the rows from fee_bands.
Please suggest

[Updated on: Tue, 16 December 2008 23:47]

Report message to a moderator

Re: Trigger help required [message #376332 is a reply to message #376314] Wed, 17 December 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 character width. Use the "Preview Message" button to verify.

Are you aware that between "SELECT MAX(REC_SEQ)..." and the following statement max value may have changed?

Regards
Michel
Re: Trigger help required [message #376984 is a reply to message #376332] Fri, 19 December 2008 05:51 Go to previous messageGo to next message
N.SasiKanth
Messages: 35
Registered: June 2003
Member
Yes im aware of that
Re: Trigger help required [message #377003 is a reply to message #376984] Fri, 19 December 2008 07:06 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you are aware that your code will not work without leading to inconsistencies.

Regards
Michel
Previous Topic: GROUP BY clause problem (merged 3)
Next Topic: selecting by adding a value to comma separated record
Goto Forum:
  


Current Time: Thu Dec 08 08:18:36 CST 2016

Total time taken to generate the page: 0.20145 seconds