Mastering GBase 8s Triggers: A Practical Guide to REFERENCING NEW and OLD for Smarter Data Control

[Sascha]

In modern database systems, triggers are powerful tools that allow automatic execution of logic in response to data changes.

In GBase 8s database, triggers become even more flexible with the use of:

• REFERENCING NEW AS NEW
• REFERENCING OLD AS OLD

These clauses let developers precisely access before and after values of data changes, enabling advanced business logic implementation.

---

🚀 1. What is a Trigger in GBase 8s?​

A trigger is a database object that automatically executes when specific events occur:

• INSERT
• UPDATE
• DELETE

👉 It is commonly used for:

• Audit logging
• Data validation
• Business rule enforcement
• Historical tracking

---

🧠 2. Why REFERENCING Matters​

Without referencing:

• You cannot clearly distinguish old vs new values
• Logic becomes harder to maintain
• Code readability decreases

With referencing:

REFERENCING NEW AS NEW
REFERENCING OLD AS OLD

`

👉 You can explicitly access:

Keyword	Meaning
NEW	Newly inserted/updated values
OLD	Previous values before update/delete

---

📊 3. Example Table Setup​

sql id="gbase_trigger_table"
CREATE TABLE t_sale (
f_saleid INT,
f_productname VARCHAR(50),
f_qty INT
);

sql id="gbase_log_table"
CREATE TABLE t_log (
f_saleid INT,
f_productname VARCHAR(50),
f_oldqty INT,
f_newqty INT
);

---

✏️ 4. Using NEW Data in INSERT Trigger​

Create Trigger​

sql id="gbase_trigger_new"
CREATE TRIGGER trg_sale_insert
AFTER INSERT ON t_sale
REFERENCING NEW AS NEW
FOR EACH ROW
(
INSERT INTO t_log (f_saleid, f_productname, f_newqty)
VALUES (NEW.f_saleid, NEW.f_productname, NEW.f_qty)
);

---

Insert Data​

sql id="gbase_insert_trigger"
INSERT INTO t_sale VALUES (1, 'Phone', 10);

---

👉 Result:

• t_sale stores the new record
• t_log records inserted data automatically

---

🔄 5. Using OLD Data in UPDATE Trigger​

Create Trigger​

sql id="gbase_trigger_old"
CREATE TRIGGER trg_sale_update
AFTER UPDATE OF f_qty ON t_sale
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
(
INSERT INTO t_log (f_saleid, f_productname, f_oldqty, f_newqty)
VALUES (
OLD.f_saleid,
OLD.f_productname,
OLD.f_qty,
NEW.f_qty
)
);

---

Update Data​

sql id="gbase_update_trigger"
UPDATE t_sale
SET f_qty = 20
WHERE f_saleid = 1;

---

📊 6. What Happens Internally?​

When the update runs:

1. OLD values are captured before modification
2. NEW values represent updated data
3. Trigger executes automatically
4. Log table records both states

---

🧩 7. Real-World Use Cases​

✔ Audit Logging​

Track every change in sensitive tables

---

✔ Inventory Systems​

Monitor stock changes:

• Before quantity
• After quantity

---

✔ Financial Systems​

Ensure traceability of:

• Balance updates
• Transaction modifications

---

⚠️ 8. Common Mistakes​

❌ Confusing NEW and OLD​

sql
OLD.f_qty -- before change
NEW.f_qty -- after change

---

❌ Missing REFERENCING clause​

👉 Leads to unclear or invalid variable access

---

❌ Overusing triggers​

• Can slow down bulk operations
• Harder to debug complex logic

---

⚡ 9. Best Practices​

• Keep trigger logic lightweight
• Use triggers only for critical business rules
• Always log meaningful changes only
• Avoid heavy computations inside triggers

---

🧠 10. Key Insight​

In GBase 8s database, triggers are not just automation tools—they are:

A mechanism for enforcing data integrity at the database layer.

REFERENCING NEW and OLD gives you precise control over data evolution, making triggers far more powerful and expressive.

---

📌 Final Thoughts​

Understanding REFERENCING NEW AS NEW and REFERENCING OLD AS OLD is essential for:

• Building audit systems
• Tracking data changes
• Implementing enterprise-level business logic

When used correctly, triggers become a silent but powerful layer of logic inside the database.

Источник: Mastering GBase 8s Triggers: A Practical Guide to REFERENCING NEW and OLD for Smarter Data Control https://dev.to/scale_b4260f8ecad7f02306d/mastering-gbase-8s-triggers-a-practical-guide-to-referencing-new-and-old-for-smarter-data-control-57ja