{"id":2906,"date":"2019-08-11T21:05:04","date_gmt":"2019-08-12T04:05:04","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2906"},"modified":"2025-06-01T07:50:49","modified_gmt":"2025-06-01T14:50:49","slug":"oracle-statement-level-triggers","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/oracle-statement-level-triggers\/","title":{"rendered":"Oracle Statement-level Triggers"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the CREATE TRIGGER<\/code> statement to create a new statement-level trigger in the database.<\/p>\n\n\n\n

Introduction to Oracle Statement-level triggers #<\/a><\/h2>\n\n\n\n

A statement-level trigger is fired whenever a trigger event occurs on a table regardless of how many rows are affected. In other words, a statement-level trigger executes once for each transaction.<\/p>\n\n\n\n

For example, if you update 1,000 rows in a table, then a statement-level trigger on that table would only be executed once.<\/p>\n\n\n\n

Due to its features, a statement-level trigger is not often used for data-related activities like auditing the data changes in the associated table. It’s typically used to enforce extra security measures on the kind of transaction that may be performed on a table.<\/p>\n\n\n\n

By default, the statement CREATE TRIGGER <\/code>creates a statement-level trigger when you omit the FOR EACH ROW<\/code> clause.<\/p>\n\n\n\n

Here is the basic syntax of creating a statement-level trigger:<\/p>\n\n\n

CREATE<\/span> [OR REPLACE<\/span>] TRIGGER<\/span> trigger_name\n    {BEFORE<\/span> | AFTER<\/span> } triggering_event \n    ON<\/span> table_name<\/span>\n    [FOLLOWS | PRECEDES another_trigger]\n    [ENABLE<\/span> \/ DISABLE<\/span> ]\n    [WHEN<\/span> condition]\nDECLARE<\/span>\n    declaration statements\nBEGIN<\/span>\n    executable statements\nEXCEPTION<\/span>\n    exception_handling statements\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the meanings of each clause are already explained in the trigger tutorial.<\/p>\n\n\n\n
Oracle Statement-level Trigger example #<\/a><\/h2>\n\n\n\n
We’ll use the table customers<\/code> from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"customers<\/figure>\n\n\n\n
Suppose, you want to restrict users from updating the credit of customers from the 28th to the 31st of every month so that you can close the financial month.<\/p>\n\n\n\n
To enforce this rule, you can use this statement-level trigger:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> TRIGGER<\/span> customers_credit_trg\n    BEFORE<\/span> UPDATE<\/span> OF<\/span> credit_limit  \n    ON<\/span> customers\nDECLARE<\/span>\n    l_day_of_month NUMBER;\nBEGIN<\/span>\n    -- determine the transaction type<\/span>\n    l_day_of_month := EXTRACT(DAY<\/span> FROM<\/span> sysdate);\n\n    IF<\/span> l_day_of_month BETWEEN<\/span> 28<\/span> AND<\/span> 31<\/span> THEN<\/span>\n        raise_application_error(-20100<\/span>,'Cannot update customer credit from 28th to 31st'<\/span>);\n    END<\/span> IF<\/span>;\nEND<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the trigger.<\/p>\n\n\n\n
First, create a new trigger customers_credit_trg<\/code>. The OR REPLACE<\/code> modifies the trigger if it already exists:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> TRIGGER<\/span> customers_credit_trg\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, instruct the Oracle to fire the trigger only before update event for the credit_limit<\/code> column of the customers<\/code> table. If you update values in other columns rather than the credit_limit column, the trigger will not execute.<\/p>\n\n\n
BEFORE<\/span> UPDATE<\/span> OF<\/span> credit_limit  \n    ON<\/span> customers\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, declare a variable to hold the current day of the month:<\/p>\n\n\n
DECLARE<\/span>\n    l_day_of_month NUMBER;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
l_day_of_month := EXTRACT(DAY<\/span> FROM<\/span> sysdate);\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, check if the current day of the month is between the 28th and 31st, and use the procedure raise_application_error to raise a user-defined error:<\/p>\n\n\n
IF<\/span> l_day_of_month BETWEEN<\/span> 28<\/span> AND<\/span> 31<\/span> THEN<\/span>\n    raise_application_error(-20100<\/span>,'Cannot update customer credit from 28th to 31st'<\/span>);\nEND<\/span> IF<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Testing the Oracle statement-level trigger #<\/a><\/h3>\n\n\n\n
The following statement uses the UPDATE<\/a><\/code> statement to increase the credit limit of all customers 10%:<\/p>\n\n\n
UPDATE<\/span> \n    customers \nSET<\/span> \n    credit_limit = credit_limit * 110<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Oracle issued the following error:<\/p>\n\n\n
ORA-20100<\/span>: Cannot update<\/span> customer credit from<\/span> 28<\/span>th to<\/span> 31<\/span>st\nORA-06512<\/span>: at \"OT.CUSTOMERS_CREDIT_TRG\", line<\/span> 8<\/span>\nORA-04088<\/span>: error during execution of<\/span> trigger<\/span> 'OT.CUSTOMERS_CREDIT_TRG'<\/span>\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned about the Oracle statement-level triggers and how to use the CREATE TRIGGER<\/code> statement to create a new statement-level trigger in the database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Note that Oracle automatically rollbacks the update because we call the raise_application_error<\/a><\/code> procedure inside the trigger.<\/p>\n\n\n\n
After that, get the current day of the month using the EXTRACT()<\/code><\/a> function:<\/p>\n\n\n