How to create a Trigger in oracle?

Question

I have oracle database 11gR2 and i want to create some triggers automatically with a block . when i want to run query in below , i have an error .

My block is :

declare
  tablesid number ;
  tablenames varchar2(4000);
  cursor c1 is
  select id , title from hr.tables;

begin
    open c1;
    loop
      fetch c1 into tablesid , tablenames;
     CREATE OR REPLACE TRIGGER tablenames||'_before_insert'
      before insert
      on hr.tablenames
      DECLARE

     columnid number ;
      columnval number ;
      columntitle varchar(4000);

      cursor c2 is
      select id from hr.columns where tableid = tablesid ;   
      BEGIN

      -- open c2 ;
       for rec in c2 loop
       select title into columntitle from hr.columns where id = rec.id
       insert into hr.rowaction(columnid,newvalue,oldvalue,actiondate)
       (
         select  id ,:new.columntitle,:old.columntitle,sysdate  
         from hr.tabletiltes
         where id = rec.id
      )
      select title into columntitle from hr.columns where id = rec.id;
      dbms_output.put_line(columntitle);
     end loop;
    end;
    end loop ;close c1; end;

ORA-06550: line 11, column 6: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action : Bind Variable "new" is NOT DECLARED anonymous block completed Bind Variable "new" is NOT DECLARED anonymous block completed

Thanks

From the comment, saeed.sh asked a procedure to build triggers that will save data when it changes, for every table of the database. This is big solution asked. — J. Chomel

jim mcnamara jim mcnamara · Accepted Answer · 2013-11-09T09:15:54

A trigger is a standalone separate piece of code that is usually considered DDL, the language that defines metadata for objects.

You cannot have a trigger declaration embedded inside a PL/SQL block. IF you need to CREATE some PL/SQL code on the fly - not a great idea - consider the DMBS_DDL.CREATE_WRAPPED procedure. You seem to have EXECUTE IMMEDIATE in mind as well. If so, have a read on this: (http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_ddl.htm)

You should define your trigger BEFORE running your PL/SQL in other words. Make two scripts.

How to create a Trigger in oracle?

1 Answers