select
‘CREATE OR REPLACE TRIGGER ‘|| trigger_name || chr(10)||
decode( substr( trigger_type, 1, 1 ),
‘A’, ‘AFTER ‘, ‘B’, ‘BEFORE ‘, ‘I’,
‘INSTEAD OF ‘ ) ||
triggering_event || ‘ ON ‘ || table_owner || ‘.’ ||
table_name || chr(10) || REF_CLAUSE || chr(10) ||
decode( instr( trigger_type, ‘EACH ROW’ ), 0, null,
‘FOR EACH ROW’), trigger_body
from (
select trigusr.name owner, trigobj.name trigger_name,
decode(t.type#, 0, ‘BEFORE STATEMENT’,
1, ‘BEFORE EACH ROW’, 2, ‘AFTER STATEMENT’,
3, ‘AFTER EACH ROW’, 4, ‘INSTEAD OF’,
‘UNDEFINED’) trigger_type,
decode(t.insert$*100 + t.update$*10 + t.delete$,
100, ‘INSERT’, 010, ‘UPDATE’, 001, ‘DELETE’,
110, ‘INSERT OR UPDATE’, 101, ‘INSERT OR DELETE’,
011, ‘UPDATE OR DELETE’,
111, ‘INSERT OR UPDATE OR DELETE’,
‘ERROR’) triggering_event,
tabusr.name table_owner, tabobj.name table_name,
‘REFERENCING NEW AS ‘||t.refnewname||’ OLD AS ‘||t.refoldname REF_CLAUSE,
t.whenclause,decode(t.enabled, 0, ‘DISABLED’, 1, ‘ENABLED’, ‘ERROR’) STATUS,
t.definition , t.action# trigger_body
from obj$ trigobj, obj$ tabobj, trigger$ t,
user$ tabusr, user$ trigusr
where (trigobj.obj# = t.obj# and
tabobj.obj# = t.baseobject and
tabobj.owner# = tabusr.user# and
trigobj.owner# = trigusr.user# and
bitand(t.property, 63) < 8 ))
where table_owner=upper(‘EASKINGDEE’)
order by owner, trigger_name