Play Store Application link – SQL in 18 steps – App on Google Play
In the world of databases, sometimes we need to perform specific actions automatically when certain events occur. This is where SQL Triggers come into play. Think of them as “database alarms” that get triggered before or after a particular action (like inserting, updating, or deleting a record).
What are SQL Triggers?
A trigger is a piece of SQL code that executes automatically in response to certain events on a table. These events can be:
- INSERT (When a new record is added)
- UPDATE (When a record is modified)
- DELETE (When a record is removed)
Triggers help in enforcing business rules, maintaining audit logs, and automating tasks like updating a related table when a change occurs.
Types of SQL Triggers
Triggers can be executed before or after a specific event:
1. BEFORE INSERT Trigger
Executes before a new record is inserted into the table. Useful for validation or setting default values.
Example: Automatically capitalize names before inserting into a users table (MySQL):
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);
2. AFTER INSERT Trigger
Executes after a new record is inserted. Useful for logging or updating another table.
Example: Log new user registrations (MySQL):
CREATE TRIGGER after_insert_users
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO user_logs(user_id, action, timestamp)
VALUES (NEW.id, 'User Registered', NOW());
3. BEFORE UPDATE Trigger
Executes before a record is updated. Useful for tracking changes.
Example: Ensure salary never decreases (MySQL):
CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
IF NEW.salary < OLD.salary THEN
SET NEW.salary = OLD.salary;
END IF;
4. AFTER UPDATE Trigger
Executes after a record is updated. Useful for maintaining audit logs.
Example: Track salary updates (Oracle PL/SQL):
CREATE OR REPLACE TRIGGER after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
5. BEFORE DELETE Trigger
Executes before a record is deleted. Useful for preventing accidental deletions.
Example: Prevent deleting admin users (MySQL):
CREATE TRIGGER before_delete_admins
BEFORE DELETE ON users
FOR EACH ROW
IF OLD.role = 'admin' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin users cannot be deleted';
END IF;
6. AFTER DELETE Trigger
Executes after a record is deleted. Useful for logging deletions.
Example: Log deleted orders (SQL Server T-SQL):
CREATE TRIGGER after_delete_orders
ON orders
AFTER DELETE
AS
INSERT INTO order_logs(order_id, action, deleted_at)
SELECT id, 'Order Deleted', GETDATE() FROM deleted;
Differences in Trigger Syntax (MySQL vs Oracle)
| Feature | MySQL | Oracle |
|---|---|---|
| Create Trigger Syntax | CREATE TRIGGER ... | CREATE OR REPLACE TRIGGER ... |
| Referencing Old & New Values | NEW.column_name, OLD.column_name | :NEW.column_name, :OLD.column_name |
| Multi-statement Triggers | Requires BEGIN ... END | Uses BEGIN ... END; |
When to Use SQL Triggers?
✅ Enforcing business rules automatically (e.g., preventing negative balances in a bank account). ✅ Logging changes for auditing purposes. ✅ Maintaining data consistency across multiple tables. ✅ Automating workflows, like sending emails when new orders are placed.
🚨 Avoid Overusing Triggers! Too many triggers can slow down database performance and make debugging difficult.
Conclusion
SQL Triggers are powerful tools for automating actions in a database. They help maintain consistency, enforce rules, and track changes. However, they should be used wisely to avoid performance issues. By understanding when and how to use them effectively, you can make your database operations more efficient!
I am not sure where you’re getting your information, but great topic. I needs to spend some time learning more or understanding more. Thanks for fantastic info I was looking for this info for my mission.
It’s very straightforward to find out any matter on web as compared to books, as I found this piece of writing at this web site.
**backbiome**
Mitolyn is a carefully developed, plant-based formula created to help support metabolic efficiency and encourage healthy, lasting weight management.
**neurosharp**
Neuro Sharp is an advanced cognitive support formula designed to help you stay mentally sharp, focused, and confident throughout your day.
**boostaro reviews**
Boostaro is a purpose-built wellness formula created for men who want to strengthen vitality, confidence, and everyday performance.
**prostafense official**
ProstAfense is a premium, doctor-crafted supplement formulated to maintain optimal prostate function, enhance urinary performance, and support overall male wellness.
**mounja boost**
MounjaBoost is a next-generation, plant-based supplement created to support metabolic activity, encourage natural fat utilization, and elevate daily energywithout extreme dieting or exhausting workout routines.
**herpafend official**
Herpafend is a natural wellness formula developed for individuals experiencing symptoms related to the herpes simplex virus. It is designed to help reduce the intensity and frequency of flare-ups while supporting the bodys immune defenses.
I simply could not go away your website prior to suggesting that I actually enjoyed the standard info an individual supply in your visitors? Is going to be back regularly to investigate cross-check new posts
Greetings! Very helpful advice in this particular post! It is the little changes that will make the most important changes. Many thanks for sharing!
Hi there to all, how is all, I think every one is getting more from this web site, and your views are good for new viewers.
Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! However, how can we communicate?
Hi there! Do you use Twitter? I’d like to follow you if that would be okay. I’m absolutely enjoying your blog and look forward to new updates.
As I site possessor I believe the content material here is rattling wonderful , appreciate it for your efforts. You should keep it up forever! Best of luck.
These are genuinely fantastic ideas in regarding blogging. You have touched some good points here. Any way keep up wrinting.
I am sure this post has touched all the internet viewers, its really really fastidious paragraph on building up new website.
Thank you, I have just been looking for information about this subject for ages and yours is the greatest I have found out till now. But, what concerning the conclusion? Are you sure concerning the supply?
I am regular reader, how are you everybody? This post posted at this web site is truly pleasant.
I am sure this piece of writing has touched all the internet users, its really really fastidious article on building up new website.
You made some really good points there. I checked on the net to find out more about the issue and found most people will go along with your views on this site.
Il nostro servizio consente la selezione di persone per attività a rischio.
Gli interessati possono scegliere esperti affidabili per missioni singole.
Tutti i lavoratori sono valutati con severi controlli.
sonsofanarchy-italia.com
Utilizzando il servizio è possibile leggere recensioni prima di assumere.
La sicurezza continua a essere un nostro impegno.
Iniziate la ricerca oggi stesso per trovare il supporto necessario!