Oracle trigger)

 

SQL code

-- Trigger

-- 1. DML trigger DML statements (DELETE, INSERT, UPDATE)

/*

A dml SQL statement may have multiple or only one row. The statement trigger triggers each SQL statement. The trigger is executed only once. The Row-Level Trigger is triggered once for each row.

Trigger.

DML trigger definition:

CREATE [or replace] TRIGGER trigger_name

{BEFORE | AFTER} -- trigger time, BEFORE or AFTER a dml SQL statement

{INSERT | UPDATE | DELETE] -- trigger event, add, DELETE, modify, or any combination of three

ON table_name -- a function table. A trigger can only work with one table. A table can have multiple triggers. However, the more triggers, the more dml efficiency will be affected.

[Referenciing old as old new as new] -- updates data and uses less settings for NEW and OLD data reference Variables

[For each row] -- specifies whether to be a ROW-Level Trigger

[WHEN...] -- WHEN the specified condition is met, the trigger body will execute

[DECLARE...] -- DECLARE a block

BEGIN

-- Executable Block

-- Do not use dml SQL FOR THE on table above. This will cause recursive triggering and infinite loops.

...Exe cutable statements...

END [trigger_name];

*/

 

-- Statement trigger statement-level trigger creates a statement trigger for a DML operation on the table. The target is the entire table.

Create or replace trigger emp_t_1

BEFORE INSERT OR UPDATE OR DELETE ON EMP

BEGIN

CASE -- determine the type of trigger

WHEN INSERTING THEN

Dbms_output.put_line ('EMP _ t_1 insert triggerd ');

WHEN UPDATING THEN

Dbms_output.put_line ('EMP _ t_1 update triggerd ');

WHEN DELETING THEN

Dbms_output.put_line ('EMP _ t_1 delete triggrad ');

End case;

END;

-- Execute the following update statement. The above trigger will be penalized

Update emp set sal = sal * 1 where empno = 7788;

 

 

-- After statement trigger

/*

Exp: count the number of DML operations on a table. after a DML operation is performed, the after trigger will count more than 1

*/

-- Create a statistical table

Create table count_dml (

Id int, table_name varchar2 (30), nums int, dt date

);

Create or replace trigger emp_t_2

AFTER INSERT OR UPDATE OR DELETE ON EMP

DECLARE

V_count int; -- declares the variable of the last record.

BEGIN

Select nums into v_count from count_dml where lower (table_name) = 'emp'; -- NO_DATA_FOUND exception

IF v_count = 0 THEN -- IF this table does not have the corresponding records, add a new

Insert into count_dml values (1, 'emp', 0, sysdate );

End if;

Update count_dml set nums = (v_count + 1) where lower (table_name) = 'emp ';

Dbms_output.put_line (v_count + 1 );

END;

Update emp set sal = sal * 1 where empno = 7788;

Commit;

 

 

-- ROW-Level Trigger. Add for each row to the creation statement.

Create or replace trigger emp_t_3

Before update on emp for each row -- for each row is defined as a ROW-Level Trigger. How many rows of dml SQL statements are affected and the trigger is executed?

BEGIN

/*

You can use new. columName to reference the column value of new data in the row-Level Trigger block.

: Old. columName reference the column value of the old data

Different types of triggers, new and old, may replace different contents.

Insert: only new, no old. old is null

Update: both old and new are available.

Delete: only old has no new

*/

Dbms_output.put_line (: new. sal | '--' |: old. sal );

END;

-- Execute the following SQL statement. The trigger defined above will trigger three times.

Update emp set sal = sal * 1.1 where emp. deptno = 10; -- 3 rows affects

 

 

-- Execute the trigger statement only WHEN certain conditions are met, and use the WHEN statement to limit

Create or replace trigger emp_t_4

Before update on emp

-- Old and new are the default values for referencing new and old data, which can be explicitly specified here

Referencing old as emp_old new as emp_new

FOR EACH ROW

-- If the WHEN statement is limited to empno = 7788, the trigger is executed. If the when statement references the column value, ':' is not added before it ':';

WHEN (emp_new.empno = 7788)

BEGIN

Dbms_output.put_line (: emp_new.ename | '--' |: emp_old.empno );

END;

-- Trigger: Only 7788 of the preceding statements are executed.

Update emp set sal = sal * 1.1 where emp. deptno = 20;

 

/** New and old triggers can be used for before and after triggers. The before trigger can modify the column value in new, but not in after,

Because the SQL statement in trigger has been executed and takes effect. If the column value in new is modified in before, it can be seen in after.

 

Statement trigger (statement-level trigger) and row-level trigger (row-level trigger) execution sequence:

Of course, the overall sequence is to execute BEFORE triggers first, and then AFTER triggers.

If the execution sequence of each trigger appears in a table

BEFORE statement trigger

BEFORE row-Level Trigger

AFTER row-Level Trigger

... Multiple rows are affected, and the row-Level Trigger is executed multiple times.

AFTER statement trigger

*/

 

 

-- The code in the trigger should be as simple as possible. If complicated, you can store complicated statements in the process. The trigger only calls the process.

 

 

-- 2 DDL trigger DDL statements (CREATE, ALTER, DROP)

/*

The DDL trigger does not act on a specific table, but on a SCHEMA. | DATABASE

DDL triggers are mainly used to record DDL operations,

Of course, exceptions, transaction rollback, and DDL operation failures can also be generated in the trigger.

Oracle Database System Event attribute functions:

Ora_client_ip_address: Client IP Address

Ora_database_name: name of the current database

Ora_dict_obj_name: name of the database object corresponding to the DDL operation

Ora_dict_obj_owner: the owner of the DDL operation object.

Ora_dict_obj_type: Type of the database object corresponding to the DDL operation

Ora_login_user: logon Username

Ora_sysevent: name of the trigger's System Event

...

Use these attributes to describe DDL operations

*/

-- Create a table that records ddl operations

Create table ddl_records (

Event varchar2 (30 ),

Username varchar2 (30 ),

Owner varchar2 (30 ),

Objname varchar2 (20 ),

Objtype varchar2 (10 ),

D_date date

)

 

Create or replace trigger ddl_t

After ddl on schema -- DLL can also be any combination of CREATE, DROP, and ALTER. The target object can be the current SCHEMA or DATABASE.

BEGIN

Insert into ddl_records values (

Ora_sysevent, ora_login_user, ora_dict_obj_owner,

Ora_dict_obj_name, ora_dict_obj_type, sysdate );

END;

-- Execute a ddl operation

Create table test_ddl_triger (id int );

-- Insert a data entry in the ddl_records table

/*

Event: create, username: scott, owner: scott

Objname: test_ddl_triger, objtype: table

*/

Drop table test_ddl_triger; -- insert another data entry

Commit;

 

 

-- 3. Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

/*

System event triggers and DML triggers have similar functions to record database changes.

However, some of these triggers have restrictions on BEFORE post-"AFTER:

The STARTUP event can only have an AFTER trigger.

SHUTDOWN can only have BEFORE triggers

LOGON can only have AFTER triggers

LOGOFF can only have BEFORE triggers

SERVERERROR can only have an AFTER trigger

*/

-- After servererror trigger

/*

The following error will not be triggered

ORA-00600 oracle internal error

ORA-01034 oracle unavailable

No data found in ORA-01403

ORA-01422 returns multi-row data

ORA-01423

ORA-04030

-- Trigger will not fix errors

-- Built-in function to obtain exception information

Ora_server_error (index): error number returned. If no value is found, 0 is returned.

Ora_is_servererror (number) error number is in the exception stack, that is, whether the current exception contains the specified exception

Number of errors in the ora_server_error_depth exception

Ora_server_error_msg (index) error message

...

*/

Create or replace trigger error_echo

AFTER SERVERERROR ON SCHEMA

DECLARE

Num number: = SQLCODE;

BEGIN

FOR I IN 1 .. ora_server_error_depth LOOP

Dbms_output.put_line ('error code: '| ora_server_error (I ));

Dbms_output.put_line ('error message: '| ora_server_error_msg (I ));

End loop;

END;

 

/*

Create or replace trigger ddl_echo

AFTER DDL ON SCHEMA

BEGIN

Dbms_output.put_line (SQLCODE );

IF ora_is_servererror (SQLCODE) THEN

Dbms_output.put_line ('error: '| sqlerrm );

ELSE

Dbms_output.put_line ('OK:' | sqlerrm );

End if;

END;

*/

-- Test the error_echo trigger function

Create table t_t (t number );

Drop table t_t;

Commit;

Insert into t_t values ('xx'); -- an exception occurs and is printed.

 

 

 

-- Maintenance trigger

 

-- View trigger information

-- User_triggers data dictionary view, which basically includes the information used by the trigger Definition

Select * from user_triggers;

-- The current user has the permission to view all the triggers

Select * from all_triggers;

 

-- Make the trigger invalid

Alter trigger trigger_name DISABLE;

 

-- Reactivate a trigger

Alter trigger trigger_name ENABLE;

 

-- For a table

-- Disable all triggers

Alter table table_name disable all triggers;

-- Activate all

Alter table table_name enable all triggers;

 

-- Re-compile the trigger

Alter trigger trigger_name COMPILE;

 

-- Delete a trigger

Drop trigger trigger_name;

 

 

 

 

/*

Reference:

Http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg13trg.htm

Http://psoug.org/reference/system_events.html

*/