Differences Between Procedures and Triggers

Procedure #

Stored procedures allow you to pre-define a series of SQL commands on the database server, enabling simple execution of those SQL commands by running the procedure.

Within a procedure, you can write not only SQL statements but also control commands like if and while loops, allowing you to create a type of program.

Trigger #

Triggers are created on tables. When a row is inserted, updated, or deleted in a table, if a trigger is set, its SQL statements are automatically executed.

This feature allows you to automatically maintain a consistent state in the database by manipulating related tables alongside data additions, deletions, or modifications.

Differences #

Procedure

• Uses CREATE PROCEDURE syntax
• Generates source code and executable code upon creation
• Executed with the EXECUTE command
• Can COMMIT and ROLLBACK

Trigger

• Uses CREATE TRIGGER syntax
• Generates source code and executable code upon creation
• Automatically executed after creation
• Cannot COMMIT or ROLLBACK