Creating a Trigger After 100 Updates: An In-Depth Guide for Oracle Database Administrators

==================================================================================

In Oracle databases, triggers are powerful tools that allow you to automate various tasks whenever specific conditions are met. One such scenario is when an update operation occurs on a table after it has been executed more than or equal to 100 times. This article will walk through the process of creating a trigger in PL/SQL (Procedural Language/Structured Query) that fires immediately before any UPDATE statement affects a specified table, provided this event happens for at least the hundredth time.

Step-by-Statement Breakdown

------------------------

### Step 1: Define Trigger Name and Specify Before Update Clause

The first step is to specify the name of your trigger. This should be done after using the `CREATE TRIGGER` keywords followed by a space. For example, let's call our trigger 'my_trigger'. Then comes the keyword `BEFORE`, which indicates that you want this trigger to execute before any subsequent updates are applied.

```sql

CREATE OR REPLACE TRIGGER my_trigger

BEFORE UPDATE

```

### Step 2: Specify Table Name

Next up is specifying the name of the table on which your trigger will be triggered. To do so, simply replace `my_table` with the actual name of your desired table within single quotes.

```sql

ON my_table

```

### Step FOR EACH ROW Clause (Optional)

While not strictly necessary for this example, you may want to include a `FOR EACH ROW` clause immediately after specifying the table name if you only wish for each individual row being updated to trigger the stored procedure instead of it firing once per update across all rows.

```sql

WHEN (ora_rows_deleted > 0 OR ora_rows_inserted > 0)

```

### Step 3: Define Trigger Body

Finally, we reach the meat and potatoes – defining what happens when our conditions are met. This is where you can include one or more statements that will be executed whenever a triggerable event occurs.

```sql

BEGIN

-- Your logic goes here;

END;

```

Example Use Case

--------------

Let's say we have an `orders` table and want to send an email notification every time someone attempts to update their order status, but only if this is the hundredth or greater attempt. Here's how you could achieve that:

```sql

CREATE TABLE orders (

id NUMBER PRIMARY KEY,

customer_name VARCHAR2(100),

order_status VARCHAR2(20)

);

INSERT INTO orders (id, customer_name)

VALUES (1, 'John Doe');

FOR i IN 0..99 LOOP

UPDATE orders SET order_status = CASE WHEN MOD(i+1,10)=0 THEN 'SHIPPED' ELSE NULL END WHERE id=i+1;

END;

CREATE OR REPLACE TRIGGER send_notification_on_update_after_100_attempts

BEFORE UPDATE ON orders

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('Trigger fired!');

END;

UPDATE orders SET order_status = CASE WHEN MOD(101,10)=0 THEN 'SHIPPed' ELSE NULL END WHERE id=102;

```

-------

In conclusion, by following these steps and carefully crafting your trigger's logic within the `BEGIN`/`END` block, you'll be well on your way to automating complex business rules in Oracle databases. Remember that triggers can have significant performance implications if not used judiciously – use them sparingly but effectively!

Create trigger after 100 updates