CODEWITHSUNDEEP
oracle-databaseplsqltriggers
vitaminJava
vitaminJava

Reputation: 209

How to rollback a column and its trigger in plsql?

I have a litte task. Firstly I added a column in my table with specific constraints. Then I added a trigger for other jobs. But I need a rollbacksql and have no idea what to proceed. Can anybody help or give an advice about it? I am adding my sql snippet.

    ALTER TABLE FCBSADM.GL_DEF ADD GL_TP NUMBER;

    ALTER TABLE FCBSADM.GL_DEF ADD CONSTRAINTS CH_COL CHECK (GL_TP between 1 and 10);

    CREATE OR REPLACE TRIGGER GL_DEF_GL_TP_TRG
   BEFORE INSERT OR
          DELETE OR
          UPDATE OF CDATE, CMPNY_DEF_ID, CUSER, DESCR, GL_DEF_ID, MNY_TP_ID, ST, UDATE, UUSER, GL_TP
          ON FCBSADM.GL_DEF
   FOR EACH ROW
DECLARE
cnt number := 0;
BEGIN
   IF INSERTING
   THEN   
      IF :NEW.GL_TP = 2        
      THEN
      SELECT 1 into cnt from dual where exists( select *  
                   FROM LOOKUP_GLCODE_IND_CEZA lookup
                  WHERE     lookup.indirim_glcode = :NEW.gl_Def_id);
        IF (cnt = 1) THEN                      
         raise_application_error 
         (-20101, 'Please insert record into LOOKUP_GLCODE_IND_CEZA before inserting GL_DEF');
         END IF;
     END IF;
   END IF;
END;

Upvotes: 0

Views: 351

Answers (2)

little_amb
little_amb

Reputation: 94

A trigger and newly added table can be rolled back only by using drop and alter statements. This is ok if its being done inside a script that executes only a few times. But is highly inefficient if both drop and alter are called frequently for n number of records.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142893

What do you want to rollback? Adding a column and creating a trigger? If so, drop them, both.

alter table gl_def drop column gl_tp;

drop trigger gl_def_gl_tp_trg;

Upvotes: 1

Related Questions