Create a trigger in oracle and FAQs

Oracle creation and FAQs Oracle & nbsp; DBA & nbsp; Studio & nbsp; CREATE & nbsp; TRIGGER & nbsp; Name & nbsp; CREATE & nbsp; TRIGGER & nbsp ;&

Oracle creation and FAQs

Create a trigger in the Oracle DBA Studio tool.

CREATE TRIGGER
Name
Create trigger-CREATE a new TRIGGER

Syntax
Create trigger name {BEFORE | AFTER} {event [OR...]}
ON table for each {ROW | STATEMENT}
Execute procedure func (arguments)
Input
Name
Trigger name.
Table
Table name.
Event
INSERT, DELETE, or UPDATE.
Funcname
A user-provided function.

Output
CREATE
If the trigger is successfully created, this information is returned.

Description
Create trigger adds a new TRIGGER to the existing TRIGGER. The TRIGGER will be associated with the table and the declared function funcname will be executed.
The trigger can be declared as before or after record operations (before checking constraints and before executing INSERT, UPDATE, or DELETE) (after checking constraints and after completing INSERT, the UPDATE or DELETE operation is triggered. if a trigger is before an event, the trigger may skip the operation of the current record or change the (current) record to be inserted (only valid for INSERT and UPDATE operations ). if the trigger changes after the event, including the last insertion, update, or deletion, are "visible" to the trigger.

For more information, see the SPI and trigger chapter in the PostgreSQL programmer manual.

Note:
Create trigger is a Postgres Language extension.
Only the table owner can create a trigger for this table.

In the current version (v7.0), the STATEMENT trigger has not been implemented yet.


See drop trigger to obtain information about how to delete a TRIGGER.

Usage
Check whether the declared distributor code exists in the distributors table before inserting or updating the table films:
Create trigger if_dist_exists
Before insert or update on films FOR EACH ROW
Execute procedure check_primary_key ('did', 'distributors ', 'did ');
Move all records to the table films before deleting or updating the content of a distributor ):
Create trigger if_film_exists
Before delete or update on distributors FOR EACH ROW
Execute procedure check_foreign_key (1, 'cascade ', 'did', 'films', 'did ');
Compatibility
SQL92
There is no create trigger statement in sql92.
The second example above can be implemented using a foreign key constraint:

Create table distributors (
Did DECIMAL (3 ),
Name VARCHAR (40 ),
CONSTRAINT if_film_exists
Foreign key (did) REFERENCES films
ON UPDATE CASCADE ON DELETE CASCADE
);

Instance

Create trigger tri_emp
After insert on employees
For each row
Insert into new values (: new. sno,: new. sname,: new. sex,: new. depart)
--------------
Create trigger tri_emp_delete
After delete on employees
For each row
Delete from new where sno =: old. sno
--------------
Create trigger tri_emp_update
After update on employees
For each row
Begin
Delete from new where sno =: old. sno;
Insert into new values (: new. sno,: new. sname,: new. sex,: new. depart );
End;
--------------
NOTE: If there are multiple statements in the begin and end clauses, each sentence should be followed by a semicolon

The most recent oracle project requires a Custom trigger, but the trigger generated in the assembled SQL statement always has an error. Take the SQL statement produced to SQLPLUS for execution (oracle 10 Gb ).

Error: Warning: Trigger created with compilation errors.

Go to the Enterprise Manager to view the trigger and you will find the following error:

"Line # = 2 Column # = 128 Error Text = PL/SQL: ORA-00984: column not allowed here
Line # = 2 Column # = 1 Error Text = PL/SQL: SQL Statement ignored

"

I do not know what the error is. I search for "column not allowed here" over the network. Some people say it is a character (char) or an integer.

But how can we solve this problem?

The test table uses two tables with the same fields, named userinfo_old and uerinfo_new respectively.

The SQL trigger is as follows:

Create trigger test_trigger
After insert on userinfo_old
For each row
Begin
Insert into userinfo_new values (userinfo_old.userid, userinfo_old.username, userinfo_old.userpass, userinfo_old.sex, userinfo_old.userqq );
End;