Trigger updating inserting
If an exception is raised in a TRANSACTION COMMIT trigger, the changes made by the trigger so far are rolled back and the client application is notified, but the transaction remains active as if COMMIT had never been requested; the client application can continue to make changes and re-request COMMIT.
The CREATE TRIGGER statement is used to add triggers to the database schema.
Triggers are database operations that are automatically performed when a specified database event occurs.
A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table.
The actions that I need to do on my -- Scenario 1 CREATE OR REPLACE TRIGGER my_trg BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW BEGIN IF UPDATING THEN IF : NEW.my_col IS NULL THEN raise_application_error(...); END IF; IF : NEW.my_col2 IS NULL THEN : NEW.my_col2 := (...); END IF; END IF; IF INSERTING THEN IF : NEW.my_col3 IS NULL THEN SELECT my_seq.
PDF (US Ltr) - 37.9Mb PDF (A4) - 38.0Mb PDF (RPM) - 37.3Mb HTML Download (TGZ) - 10.3Mb HTML Download (Zip) - 10.3Mb HTML Download (RPM) - 8.9Mb Man Pages (TGZ) - 216.8Kb Man Pages (Zip) - 329.6Kb Info (Gzip) - 3.4Mb Info (Zip) - 3.4Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 If you drop a table, any triggers for the table are also dropped.
This approach can be thought of as auditing changes to a table.
Example 39-4 shows an example of an audit trigger procedure in CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Create a row in emp_audit to reflect the operation performed on emp, -- make use of the special variable TG_OP to work out the operation.
The trigger is mostly used for maintaining the integrity of the information on the database.
For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.
In addition to triggers that fire when data is modified, Oracle 10g supports triggers that fire when schema level objects (that is, tables) are modified and when user logon or logoff events occur.