Example of a Statement-Level Trigger and its comparison to a Row-Level Trigger:

Let's use a scenario where we want to log the number of rows affected by an UPDATE operation on an employees table.

1. Statement-Level Trigger Example:

A Statement-Level Trigger is fired only once per SQL statement, no matter how many rows the statement affects.

CREATEORREPLACETRIGGERupdate_log_statement_triggerAFTERUPDATEONemployeesFOREACHSTATEMENTBEGININSERTINTOupdate_log(table_name,num_of_rows)VALUES('employees',SQL%ROWCOUNT);END;

• Trigger Execution: This trigger will execute once after any UPDATE operation on the employees table, even if it updates multiple rows. It logs the total number of rows affected by the UPDATE statement using SQL%ROWCOUNT.
• Scenario: If an UPDATE statement affects 5 rows, SQL%ROWCOUNT will return 5, and the log will record this in the update_log table.

2. Row-Level Trigger Example:

A Row-Level Trigger is fired once for each row affected by the SQL statement. It allows you to perform actions on individual rows of data.

CREATEORREPLACETRIGGERupdate_log_row_triggerAFTERUPDATEONemployeesFOREACHROWBEGININSERTINTOupdate_log(table_name,affected_row_id)VALUES('employees',:OLD.employee_id);END;

• Trigger Execution: This trigger will execute once for each row that is updated. In this case, it logs the employee_id of the row that was updated.
• Scenario: If the UPDATE statement affects 5 rows, the trigger will fire 5 times—once for each updated row—and each time it will log the employee_id of the row being updated.

In Summary:

Statement-Level Trigger: Executes once per SQL statement, regardless of the number of rows affected. It's ideal for actions that don’t need access to individual rows (like counting affected rows).

Row-Level Trigger: Executes once for each row affected, making it suitable for actions that depend on specific row values (like tracking updates to individual rows).

Which one to use depends on your specific requirement:

Use a Statement-Level Trigger for actions thatshould happen once per statement, such as loggingsummary information or enforcing conditions on the overall statement.

Use a Row-Level Trigger for operations that need to workwith individual rows, such asauditing or data validation.