Trusted answers to developer questions
What are triggers in SQL?
A trigger is a stored procedure - which is a set of SQL statements saved under a name, just like a function, so that it can be reused - that is executed automatically when a certain event occurs in a database.
For instance, you may program a trigger to execute when data is inserted in a table.
Benefits
- Calculating derived columns automatically
- Ensuring referential integrity
- Logging events
- Auditing
- Replicating tables synchronously instead of the default asynchronous replication
- Authenticating users
- Preventing invalid transactions
Types of Triggers
1. Event
- DML Trigger: It fires when a
DML(Database Manipulation Language) event is specified (INSERT/UPDATE/DELETE) - DDL Trigger: It fires when a
DDL(Database Definition Language) event is specified (CREATE/ALTER) - DATABASE Trigger: It fires when a database event is specified (
LOGON/LOGOFF/STARTUP/SHUTDOWN)
2. Timing
- BEFORE Trigger: It fires before the specified event has occurred.
- AFTER Trigger: It fires after a specified event has occurred.
- INSTEAD OF Trigger: It lets you skip a statement and execute a different statement present in the trigger body instead.
3. Level
- STATEMENT level Trigger: It fires one time for a specified event statement.
- ROW level Trigger: It fires for each record that was affected in a specified event. (only for
DML)
SQL Implementation
A trigger is implemented in the given code which will be fired before anything is inserted in the people table. This trigger makes sure that a person does not have a negative age and sets the age to zero.
CREATE TABLE people (age INT,name varchar(150));delimiter //CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; ENDIF; //INSERT INTO people VALUES (-20, 'Sid'), (30, 'Josh');select * from people;
RELATED TAGS
triggers
sql
triggers in sql
database triggers
database
Copyright ©2024 Educative, Inc. All rights reserved
TRENDING TOPICS
Did you find this helpful?