oracle触发器创建格式

begin
insert into audit_creation values(audit_seq.nextval,ora_dict_obj_owner,ora_dict_obj_name,sysdate);
end audit_creation;
/
数据操作语言触发器
create [or replace] trigger trigger_name
6.连接
系统或者数据库事件触发器
create [or replace] trigger trigger_name
{before |after} database_event on {database|schema}
[declare]
declaration_statements;
begin
[declaration_statement;]
begin
execution_statements;
end after statement;]
end [trigger_name];
/
instead-of 触发器
create [or replace] trigger trigger_name
数据库定义语言触发器
create [or replace] trigger trigger_name
{before |after| instead of}ddl_event on {database|schema}
[when (logical_expression)]
[declare]
declaration_statements;
end [trigger_name];
/
判断条件 inserting、updating、deleting
在触发器中编写不可更新视图的结构:
1.集合运算符
2.聚集函数
3.case或者decode语句
4.connect by、group by、having或者start with子句
5.distinct运算符
instead of {dml_statement}
on {object_name|database|schema}
for each row
[when (logical_expression)]
[declare]
declaration_statements;
begin
execution_statements;
[pragma autonomous_transaction;]
declaration_statements;
begin
execution_statements;
end [trigger_name];
/
自治事务的理解:
pragma autonomous_transaction;
自治域加在declare块中，有了这样的预处理，就会使定义的对象为一个单独的事务域，与其他事务无关。
begin
execution_statements;
end before each row;]
[after each row is
[declaration_statement;]
begin
execution_statements;
end after each row;]
[after statement is
复合触发器
不支持when子句和pragma
create [or replace] trigger trigger_name
for {insert |update|update of column1[,column2[,column(n+1)]] |delete}
on table_name
,audit_owner_name varchar2(30) not null
,audit_obj_name varchar2(30) not null
,audit_date date not null
)
create or replace trigger audit_creation
before create on schema
compound trigger
[before statement is
[declaration_statement;]
begin
execution_statements;
end before statement;]
[before each row is
[declaration_statement;]
{before |after}
{insert |update|update of column1[,column2[,column(n+1)]] |delete}
on table_name
[for each row]
[when (logical_expression)]
[declare]
execution_statements;
end [trigger_name];
/
例子:
create or replace trigger connection_trigger
after logon on database
begin
user_connection.connecting(sys.login_user);
begin
execution_statements;
end [trigger_name];
/
create sequence audit_seq increment by 1 start with 10000
create table audit_creation
(audit_creation_id numb发器的限制
最大触发器的尺寸 32760 byte
非系统触发器主体不能包含ddl语句、dcl、transaction control language