Conditional UPDATE Triggers in Oracle

PublishedMarch 4, 2020

Comments

No comments yet. Be the first to comment.

More from this blog

SQLcl host Command

Oracle SQLcl is the command line tool to connect to an Oracle database. One neat feature it has is the ability to call host commands (i.e call terminal commands directly from SQLcl). You can use any of the following to trigger a host command: host ho...

Jul 5, 20251 min read

Tips for APEX Application Constants

How to use Text Messages for Constants

Jan 22, 20244 min read

SQL Developer for VS Code

Last week SQL Developer for VS Code was released (direct link to VS Code extension here). If you're not using VS Code (VSC) I highly suggest you download and install as it's the de facto editor for developers (regardless of programming language). I'v...

Jan 22, 20243 min read

How to Generate a sitemap.xml in SQL

I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example: <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ...

Jan 17, 20242 min read

What Happens to APEX Page Items that are Never Rendered?

Every APEX page item has a Server Side condition which controls whether the item is rendered on the page. They're various server side condition options and for the purpose of this article we'll just set it to Never (can do things like queries that re...

Jan 15, 20242 min read

TalkApex

321 posts

In Trigger Definition (update of ...)

Using the update of clause you can explicitly list the columns that will execute the trigger when updated.

create or replace trigger demo_trigger before update of col_x on demo_table for each row begin dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x); end; / set serveroutput on -- Update another column update demo_table set col_y = 'world'; 1 row updated. -- Note: trigger wasn't run as it's not in the list of columns defined in "update of ..." -- Update the "triggered" column update demo_table set col_x = 'goodby'; -- Note: This output shows the trigger was executed ***COL_X*** is being updated: goodby 1 row updated.

Checking Column (updating('col_name'))

In this example, the trigger will run for all update statements and explicit checks can be added to see if a given column is being updated.

create or replace trigger demo_trigger before update on demo_table for each row begin dbms_output.put_line('Trigger started'); if updating('col_x') then dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x); end if; end; / -- Running the same examples as first demo set serveroutput on -- Update another column update demo_table set col_y = 'world'; Trigger started 1 row updated. -- Note: trigger was run (as shown by "Trigger Started" message) but COL_X wasn't updated -- Update the "triggered" column update demo_table set col_x = 'goodby'; Trigger started ***COL_X*** is being updated: goodby 1 row updated. -- Trigger was run and COL_X was updated

Conditional UPDATE Triggers in Oracle

Comments

More from this blog

SQLcl host Command

Tips for APEX Application Constants

SQL Developer for VS Code

How to Generate a sitemap.xml in SQL

What Happens to APEX Page Items that are Never Rendered?

In Trigger Definition (`update of ...`)

Checking Column (`updating('col_name')`)

Command Palette

Comments

More from this blog

In Trigger Definition (update of ...)

Checking Column (updating('col_name'))

In Trigger Definition (`update of ...`)

Checking Column (`updating('col_name')`)