Triggers for Oracle Database (ii)

DML triggers
Refers to the body of the program that is triggered when an insert, update, or delete operation occurs. You can use DML triggers if you want to record or limit their actions when the user is working on the data.
For example, I want to count my website user's registration, logout or update personal information, and so on, we can write the following trigger, each time a user does this, the trigger will automatically execute, and in the Log Log table to store the information.


Code:
Create or Replace Trigger User_log
Before delete or insert or update
On users
For each row
Declare
ID number;
Begin
Select COUNT (*) +1 into ID from ts_log_users t;
If inserting then inserts into Ts_log_users t values (id,sysdate, (SELECT COUNT (*) +1 from ts_log_users t where t.class= ' crea Te '), ' Create ',: new.username);
elsif deleting then insert to ts_log_users t values (id,sysdate, (SELECT COUNT (*)-1 from ts_log_users where class= ' Create '), ' delete ',: new.username);
else insert into Ts_log_users t values (id,sysdate, (select T.count from Ts_log_users t where create_date= (select Max (t.cre Ate_date) from Ts_log_users t), ' Update ',: New.username);
End If;
End



The first line defines the trigger whose name is User_log,create or replace, which means to establish or overwrite if a trigger with the same name exists.

The second line of keywords can be selected before and after, that is, before and after the operation.
For example, I want to check the IP status of user login, then we need to trigger the trigger after the user log in successfully, so write after.
And here to the user's additions and deletions to change the sensitivity, so choose before. The operation definition is followed by what action triggers the trigger.

The third row is followed by the table name, which triggers the trigger when the user operates on the table.
Also, when the operation is update, you can write the before update of column name on the table name to make the restriction more specific.

The Forth line triggers are defined as row-level triggers, which trigger a trigger whenever a row of data changes.
For example, if I update the information of three users, the log Log table will generate three data.
If you do not write this sentence, the trigger will only be triggered once in the above operation, forming a single piece of data.

Row five declares variables that are used to store and manipulate some of the available data.

From begin to end for the program body, it is easy to read and understand, there are two main operations,
One is to assign the log log's data volume +1 as the data ID to the previously defined ID variable. Assignment is done with into.
The second operation is an if operation, the processing of adding and removing different cases is completed separately.
Processing mainly for the log Log table to insert data,
If you are creating a new user, insert the data ID (obtained above) into the Log Log table, the system time,
The current number of users (that is, count is the data for the create operation under the log log),
The action category (create here) and the user name of the action (: new) means the newly modified data, in contrast: old. Used, these two fields are often used when updating operations.
For example, to insert the data before the update into another table, use: old. Indicates that the newly inserted data is used: new. Operation). The latter two are operations on delete and update, similar to the one above.



Alternative triggers
Such triggers are only applied to the view. Let me give you an example of how this trigger is useful. For example, I provide the junior administrator with a view that shows only the basic registration information of the user, such as user name, gender, ethnicity, etc., and hides the secret information of the user's password, height, weight (the information exists in the table, Lao Zhang cannot see it because I didn't give him permission to look at the table). But one day there is a backdoor to find old Zhang want to add their own information to my database, so Lao Zhang on the view to insert operations. But the problem is, when inserting a connections data into the table, we need its user password, height and weight, etc. (set is not empty), and old Zhang can not see so plug in, so error can not be manipulated. So then we can write an alternative trigger, that is, if the old Zhang view operation, the following table will automatically insert some random password, etc., in order to complete the old picture operation. The trigger definition format is similar to the above and is not described here (because I don't have a view, so I didn't write it).



System triggers
is triggered when the system is operating, such as startup, DROP, alter, logon, and so on.
Below I write a, because my database gave a lot of people to use, Lao Zhang, Lao Wang and so on, and they often log in to the database at other times and places, I want to see these logins, so I write a login trigger:

Code: Create or Replace Trigger Oracle_user_log
After logon
On database
Declare
ID number;
Begin
Select COUNT (*) +1 into ID from ts_o_log_users t;
INSERT into ts_o_log_users values (id,sys.login_user,sysdate,ora_client_ip_address);
End





Some additional reference codes are attached:

Create or Replace Trigger Testtrigger
After insert on Test1trige
For each row
DECLARE V_flag number:=0;
Begin
Select COUNT (0) into V_flag from test2trige where xh_id =: new.xh;
If V_flag = 0 Then
Insert into Test2trige (XH_ID,XSXM) VALUES (: NEW.XH,:NEW.XM);
End If;
End


Create or replace trigger TRIG_JZG_RYLX after insert or update
On GXJG_JZGRYLXGX for each row
Declare
V_SQLERRM varchar (4000);
V_count_n number:=0;
Begin

Select COUNT (0) into V_count_n from [e-mail Protected]_zfoa where Yhm=:new.yhm and XYDM in (' 20140503 ', ' 20140504 ', ' 2014050 5 ', ' 20140506 ');
If V_count_n >0 and:new.yhlx not on (' 009 ', ' 001 ') Then
Update [Email Protected]_zfoa set Xydm=decode (: NEW.YHLX, ' 2 ', ' 20140503 ', ' 006 ', ' 20140504 ', ' 008 ', ' 20140505 ', ' 004 ', ' 20140506 ')--, ' 009 ', ' 20140503 ')
where Yhm=:old.yhm and Xydm=decode (: OLD.YHLX, ' 2 ', ' 20140503 ', ' 006 ', ' 20140504 ', ' 008 ', ' 20140505 ', ' 004 ', ' 20140506 ');
Update [Email Protected]k_zfoa set Xydm=decode (: NEW.YHLX, ' 2 ', ' 20140503 ', ' 006 ', ' 20140504 ', ' 008 ', ' 20140505 ', ' 004 ', ' 20140506 ')--, ' 009 ', ' 20140503 ')
where Yhm=:new.yhm and xydm= (select XYDM from [e-mail Protected]_zfoa where Yhm=:new.yhm and XYDM in (' 20140503 ', ' 20140504 ' , ' 20140505 ', ' 20140506 ');

--else
--INSERT INTO [email Protected]_zfoa
----(XYDM, JSDM, YHM, Sjlsh)
--Values
--(Decode (: NEW.YHLX, ' 2 ', ' 20140503 ', ' 006 ', ' 20140504 ', ' 008 ', ' 20140505 ', ' 004 ', ' 20140506 '), ' 7 ',: New.yhm, ' 1 ');
End If;

Select COUNT (0) into V_count_n from [e-mail Protected]_zfoa where Yhm=:new.yhm and XYDM in (' 20140503 ', ' 20140504 ', ' 2014050 5 ', ' 20140506 ');
If V_count_n =0 Then
insert INTO [email Protected]_zfoa
(XYDM, JSDM, YHM, Sjlsh)
Values
(Decode (: NEW.YHLX, ' 2 ', ' 20140503 ', ' 006 ', ' 20140504 ', ' 008 ', ' 20140505 ', ' 004 ', ' 20140506 '), ' 7 ',: New.yhm, ' 1 ');
End If;

Select COUNT (0) into v_count_n from [email Protected]_zfoa where yhm=:new.yhm;
If V_count_n >0 Then
Update [Email Protected]_zfoa set Zdm=decode (: NEW.YHLX, ' 2 ', ' 02 ', ' 004 ', ' 05 ', ' 006 ', ' 03 ', ' 008 ', ' 04 ', ' 009 ', ' 01 ', ' 001 ', ' n ') where yhm=:old.yhm;

End If;

--Exception handling
Exception
When OTHERS Then
V_SQLERRM: = (SQLERRM | | chr () | | dbms_utility.format_error_backtrace);
Insert into Xt_errlog
Values
(' Office system ',
' Bmryxxbrylx ',
' YHM: ' | | : New.yhm,
V_SQLERRM,
To_char (sysdate, ' Yyyymmddhh24miss '));
End

----Exception Handling Table structure

--Create table
CREATE TABLE Xt_errlog
(
YWXTMC VARCHAR2 (100),
YWXTBM VARCHAR2 (100),
Cwzdxx VARCHAR2 (500),
CWSJ VARCHAR2 (1000),
GXBJ VARCHAR2 (22)
)
Tablespace ZFDXC
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 448
Next 1
Minextents 1
Maxextents Unlimited
);
--ADD comments to the table
Comment on table Xt_errlog
Is ' System error log table ';
--ADD comments to the columns
Comment on column xt_errlog. Ywxtmc
Is ' Business system name (example: Data center) ';
Comment on column xt_errlog. Ywxtbm
Is ' Business system table name (example: ZYDMB) ';
Comment on column xt_errlog. Cwzdxx
Is ' shows the field that will cause an error (for example: xh:200010,bjdm:,zydm:5200) ';
Comment on column xt_errlog. Cwsj
Is ' Error time (example: Yyyymmddhh24miss) ';
Comment on column xt_errlog. Gxbj
Is ' update Mark u/i ';

Triggers for Oracle Database (ii)