Oracle tiger trigger instance

Oracle tiger trigger instance

-- Instance 1 ------------------------
-- Create a trigger. When you execute DML statements on the test table, the related information is recorded in the log table.
-- Create a test table
Create Table Test
(
T_id number (4 ),
T_name varchar2 (20 ),
T_age number (2 ),
T_sex char
);
-- Create a record test table
Create Table test_log
(
Rochelle user varchar2 (15 ),
L_type varchar2 (15 ),
Rochelle date varchar2 (30)
);
-- Create a trigger
Create or replace trigger test_trigger
After delete or insert or update on Test
Declare
V_type test_log.l_type % type;
Begin
If inserting then -- insert triggers
V_type: = 'insert ';
Dbms_output.put_line ('records have been successfully inserted and logged to logs ');
Elsif updating then -- Update trigger
V_type: = 'update ';
Dbms_output.put_line ('records have been successfully updated and logged to logs ');
Elsif deleting then
V_type: = 'delete ';
Dbms_output.put_line ('records have been successfully deleted and recorded in logs ');
End if;
Insert into test_log values (user, v_type,
To_char (sysdate, 'yyyy-mm-dd hh24: MI: ss '));
End;
/
-- Run the DML statements separately.
Insert into test values (101, 'zhao', 22, 'M ');
Update Test Set t_age = 30 Where t_id = 101;
Delete test where t_id = 101;
-- Then view the effect
Select * from test;
Select * From test_log;

-- Instance 2 ------------------------
-- Create a trigger that maps the total number of employees and total salaries of each department in the EMP table.
-- Create a ing table
Create Table dept_sal
As
Select deptno, count (empno) as total_emp, sum (SAL) as total_sal from EMP group by deptno;
Desc dept_sal;
-- Create a trigger
Create or replace trigger emp_info
After insert or update or delete on EMP
Declare
Cursor cur_emp is
Select deptno, count (empno) as total_emp, sum (SAL) as total_sal from EMP group by deptno;
Begin
Delete dept_sal; -- The ing table information is first deleted when triggering.
For v_emp in cur_emp Loop
-- Dbms_output.put_line (v_emp.deptno | v_emp.total_emp | v_emp.total_sal );
-- Insert data
Insert into dept_sal
Values (v_emp.deptno, v_emp.total_emp, v_emp.total_sal );
End loop;
End;
/
-- Perform DML operations on the EMP table
Insert into EMP (empno, deptno, Sal) values ('000000', '10', 123 );
Select * From dept_sal;
Delete E-mapreduce where e-mapreduce = 123;
Select * From dept_sal;

-- Instance 3 ------------------------
-- Create a trigger to record the deleted data in the table
-- Create a table
Create Table employee
(
Id varchar2 (4) not null,
Name varchar2 (15) not null,
Age number (2) not null,
Sex char not null
);
Desc employee;
-- Insert data
Insert into employee values ('e101 ', 'zhao', 23, 'M ');
Insert into employee values ('e101', 'jian ', 21, 'F ');
-- Create a record table
Create Table old_employee
Select * from employee;
Desc old_employee;
-- Create a trigger
Create or replace trigger tig_old_emp
After delete on employee --
For each row -- Statement-level trigger, that is, each row is triggered once.
Begin
Insert into old_employee
Values (: Old. ID,: Old. Name,: Old. Age,: Old. Sex); --: Old indicates the old value.
End;
/
-- Perform the test below
Delete employee;
Select * From old_employee;

-- Instance 4 ------------------------
-- Create a trigger to insert data using a view
-- Create a table
Create Table tab1 (TID number (4) primary key, tname varchar2 (20), Tage number (2 ));
Create Table tab2 (TID number (4), ttel varchar2 (15), tadr varchar2 (30 ));
-- Insert data
Insert into tab1 values (101, 'zhao', 22 );
Insert into tab1 values (102, 'yang', 20 );
Insert into tab2 values (101, '20140901', 'anhuisuzhou ');
Insert into tab2 values (102, '20140901', 'anhuisuzhou ');
-- Create a view to connect two tables
Create view tab_view
Select tab1.tid, tname, ttel, tadr from tab1, tab2
Where tab1.tid = tab2.tid;
-- Create a trigger
Create or replace trigger tab_trigger
Instead of insert on tab_view
Begin
Insert into tab1 (TID, tname) values (: New. TID,: New. tname );
Insert into tab2 (ttel, tadr) values (: New. ttel,: New. tadr );
End;
/
-- Now you can use the view to insert data
Insert into tab_view values (105, 'shanghaiyang', '20140901', 'beijing ');
-- View results
Select * From tab_view;

-- Instance 5 ------------------------
-- Create a trigger to compare the updated salary in the EMP table
Create or replace trigger sal_emp
Before update on EMP
For each row
Begin
 
If: Old. SAL>: New. Sal then
Dbms_output.put_line ('salary demo ');
Elsif: Old. Sal <: New. Sal then
Dbms_output.put_line ('salary increase ');
Else
Dbms_output.put_line ('salary has not changed ');
End if;
Dbms_output.put_line ('salary Before update: '|: Old. Sal );
Dbms_output.put_line ('salary after update: '|: New. Sal );
End;
/
-- Execute update to view results
Update EMP set sal = 3000 where empno = '000000 ';

-- Instance 6 ------------------------
-- Create a trigger and store the create and drop operations in the log_info table
-- Create a table
Create Table log_info
(
Manager_user varchar2 (15 ),
Manager_date varchar2 (15 ),
Manager_type varchar2 (15 ),
Obj_name varchar2 (15 ),
Obj_type varchar2 (15)
);
-- Create a trigger
Create or replace trigger trig_log_info
After create or drop on Schema
Begin
Insert into log_info
Values (user, sysdate, SYS. dictionary_obj_name, SYS. dictionary_obj_owner,
SYS. dictionary_obj_type );
End;
/
-- Test statement
Create Table A (ID number );
Create type AA as object (ID number );
/
Drop Table;
Drop type AA;
-- View results
Select * From log_info;

-- Related data dictionary -----------------------------------------------------//
Select * From user_triggers;
Select * From all_triggers;
Select * From dba_triggers; -- You must Log On As a dba to use this data dictionary.

-- Enable and disable
Alter trigger trigger_name disable;
Alter trigger trigger_name enable;