sp trigger

Summary

The lesson introduces procedural SQL concepts, then focuses on stored procedures and triggers. The instructor contrasts routines that must be called explicitly with triggers that run automatically when a specified table event occurs.

1. Procedural SQL

PL/SQL is described as an extension of SQL that adds programming features, including:

• Variables
• Conditional logic, such as IF/ELSE
• Loops and iteration
• The ability to run multiple SQL statements in sequence within a block

These features make it possible to group SQL and procedural logic into reusable database objects, including procedures, functions, packages, and triggers.

2. Stored procedures

A stored procedure is a named database object containing one or more SQL or procedural statements. It has two main parts:

• Header: The procedure declaration, name, and any parameters.
• Body: The statements enclosed by BEGIN and END.

A procedure must be defined and then explicitly called. The instructor demonstrates calling one with CALL procedure_name(...). The lesson distinguishes procedures from functions by noting that procedures do not return a value in the same way functions do.

The instructor explains how to change the SQL delimiter—using // in the examples—so semicolons inside the procedure body are not mistaken for the end of the entire definition. The delimiter is reset afterward.

Procedures are reusable: define the logic once, then call it repeatedly with different inputs.

Example: Creating several tables dynamically

The demonstration creates a procedure that accepts a table name as input, then constructs and executes three CREATE TABLE statements. The tables share a schema but use different suffixes, such as _raw, _transform, and _gold.

The procedure’s general steps are:

1. Accept the base table name as a parameter.
2. Build three SQL command strings by combining that name with the suffixes and a shared column definition.
3. Prepare each string as a SQL statement.
4. Execute the prepared statements.
5. Deallocate the prepared statements.

The instructor calls the procedure with a name such as account and checks that the three tables were created. The example shows how a procedure can automate repetitive work that would otherwise require writing similar SQL statements by hand.

3. Triggers

A trigger is a database object that runs automatically when a specified action occurs on a table. Unlike a stored procedure, it is not normally called directly.

Three elements determine when a trigger runs:

• Timing: BEFORE or AFTER the action.
• Event: INSERT, UPDATE, or DELETE.
• Object: The table on which the event must occur.

The examples place the trigger body between BEGIN and END. In the shown syntax, a trigger also specifies that it runs for each affected row.

Example: Recording changes in an audit table

The lesson uses a customer table as an example of important data that should be monitored. An audit table is created to record changes, with fields for information such as an ID, the action performed, the affected record’s data, and a timestamp.

The example workflow is:

1. Create the audit table.
2. Create an AFTER INSERT trigger on the customer table.
3. When a customer row is inserted, have the trigger add a log row containing the action, the new row’s details, and the current time.
4. Create corresponding triggers for UPDATE and DELETE if those operations also need to be tracked.
5. Test the triggers by changing customer data and checking that the audit table receives records automatically.

The instructor refers to NEW values for newly inserted or updated data and OLD values for data that existed before an update or was deleted.

4. Main comparison and practical points

• Stored procedure: Reusable logic that a user or application calls explicitly; it can accept parameters.
• Trigger: Logic attached to a table that runs automatically when its timing and event conditions are met.
• Triggers can support automatic auditing, while procedures can automate repeatable, multi-step operations.
• Access to sensitive tables and audit data should be controlled with appropriate permissions.

Technical note: The lecture calls the topic PL/SQL, but its examples use syntax commonly associated with MySQL, including delimiter changes and prepared statements. The instructor also characterizes stored procedures as providing all-or-nothing atomicity; in practice, transaction and rollback behavior depends on the database system and how the procedure is written.

Speakers and sources featured

• Primary instructor: Introduces himself as “Supan Bha” in the subtitles; the exact spelling is uncertain.
• Participants: Several attendees contribute brief spoken or chat responses. Names recognizable in the subtitles include Kanishka, Sufian, Rahul, Swetha, Mayur, and Jaya. Other names may be present, but the auto-generated subtitles are unclear.