BEFORE dml FOR EACH ROW
create table tq84_table_with_trigger (
col_1 number,
col_2 varchar2(20)
);
create trigger tq84_table_with_trigger_trg
before insert or update on tq84_table_with_trigger
for each row
begin
:new.col_2 := to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss');
end tq84_table_with_trigger_trg;
/
insert into tq84_table_with_trigger(col_1) values (1);
insert into tq84_table_with_trigger(col_1) values (2);
select * from tq84_table_with_trigger;
drop table tq84_table_with_trigger;
DDL trigger (INSTEAD OF)
create or replace trigger tq84_ddl_trigger
instead of create on schema
begin
-- execute immediate 'create table tq84_trigger_has_fired as select sysdate creation_date from dual';
dbms_output.put_line('Create @ ' || sysdate);
for r in (
select s.sid,
q.address,
q.sql_text --into sqltext_
from sys.v_$session s join
sys.v_$sqltext q on s.sql_address = q.address --and s.sql_hash_value = q.hash_value
where -- s.sid=sys_context('USERENV', 'SID')
upper(q.sql_text) like '%STRAWBERRY%'
) loop
dbms_output.put_line(r.sid);
dbms_output.put_line(r.address);
dbms_output.put_line(r.sql_text);
dbms_output.put_line('');
dbms_output.put_line('---');
dbms_output.put_line('');
end loop;
end tq84_ddl_trigger;
/
show errors
prompt
prompt
-- Not found in v$sql:
-- create package tq84_pck as
-- end tq84_pck;
-- /
create table tq84_strawberry (a number, b varchar2(10));
prompt
prompt
drop trigger tq84_ddl_trigger;
Logon trigger
The following code is a simple example that tries to demonstrate how a logon trigger might be formulated. In real life, to record user logons, aud$ would be used.
create table logon_trigger_test (usr varchar2(128), ts date);
create or replace trigger someone_logged_on
after logon on database
declare
pragma autonomous_transaction;
begin
insert into logon_trigger_test values (user, sysdate);
commit;
end someone_logged_on;
/
drop trigger someone_logged_on;
drop table logon_trigger_test;
Logon triggers on a schema
It's possible to create a trigger that just tracks the logons of a specific user (after logon on schema for the user creating the trigger, or after logon on rene.schema for the user rene):
create table my_logons (d date);
create or replace trigger logon_trg after logon on schema
begin
insert into my_logons values (sysdate);
commit;
end logon_trg;
/
A failing logon trigger on schema does not prevent the user from logging in!
Disabling logon triggers
Logon triggers (as well as other system triggers) can be disabled by setting _system_trigger_enabled to false. This could be necessary if a logon trigger was written badly and prevents users from logging in.
alter system set "_system_trig_enabled" = false scope=both;
Finding logon triggers in DBA_TRIGGERS
Catching erroneous SQL statements
The following example demonstrates how a
servererror trigger can be used to catch erroneous
SQL statements.
First, we need a table to record the timestamp, the
SQL error-code and the failing SQL statement to:
create table tq84_error (
ts timestamp,
ora_server_error varchar2(4000),
sql_stmt clob
);
Then we create the trigger that gets fired when an SQL error is encountered:
create or replace trigger tq84_error -- Triggers can have same name as other objects in schema - let's take advantage of this feature!
after servererror on schema
declare
stmt_pieces ora_name_list_t;
nof_pieces number;
stmt clob;
begin
nof_pieces := ora_sql_txt(stmt_pieces);
for piece in 1 .. nof_pieces loop
stmt := stmt || stmt_pieces(piece);
end loop;
insert into tq84_error values (
systimestamp,
ora_server_error(1),
stmt
);
commit;
exception when others then
--
-- Do not permit recursive errors.
--
null;
end;
/
Provoke an error on purpose to test the trigger:
create table tq84_foo(a number, b varchar2(20));
insert /* stmt 1 */ into tq84_foo values (1, 'one');
insert /* stmt 2 */ into tq84_foo values ('a', 'two');
-- ORA-01722: invalid number
select * from tq84_error order by ts desc;
Another (the same?) example
create table tq84_caught_errors (
dt date,
username varchar2( 30), -- value from ora_login_user
msg varchar2(4000),
stmt varchar2(4000)
);
create or replace trigger tq84_catch_errors
after servererror on database
declare
sql_text ora_name_list_t;
msg_ varchar2(4000) := null;
stmt_ varchar2(4000) := null;
begin
for depth in 1 .. ora_server_error_depth loop
msg_ := msg_ || ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
stmt_ := stmt_ || sql_text(i);
end loop;
insert into
tq84_caught_errors (dt , username ,msg ,stmt )
values (sysdate, ora_login_user,msg_,stmt_);
end;
/
----- test
select * from foijf;
select dt, username, substr(stmt, 1, 100) from tq84_caught_errors;
Determine the firing SQL statement
--
-- Determine the triggering SQL statement
-- within a trigger.
--
create table tq84_get_dml (
a number,
b varchar2(10)
);
create table tq84_get_dml_log (
dml varchar2(1000)
);
create trigger tq84_get_dml_trg_ins
before insert or update on tq84_get_dml
for each row
declare
dml varchar2(1000);
begin
select
sq.sql_text
into
dml
from
v$open_cursor cu join
v$session se on cu.saddr = se.saddr join
v$sql sq on cu.sql_id = sq.sql_id
where
se.sid = sys_context('USERENV', 'SID') and
cu.sql_exec_id is not null and
cu.sql_id != (select sq.sql_id
from v$sql sq join
v$session se on sq.address = se.sql_address and
sq.hash_value = se.sql_hash_value
where
se.sid = sys_context('USERENV', 'SID')
);
insert into tq84_get_dml_log values (dml);
end tq84_get_dml_trg_ins;
/
show errors trigger tq84_get_dml_trg_ins
insert into tq84_get_dml values (1, 'foo');
select * from tq84_get_dml_log;
drop table tq84_get_dml purge;
drop table tq84_get_dml_log purge;