Free 30-day beta access · no payment · progress saved
The business problem
A price history must be kept whenever a change occurs, regardless of the calling process.
What you will be able to do
Create and test an audit trigger with OLD, NEW, BEFORE, AFTER, ROW and STATEMENT.
Write a trigger function
Choose BEFORE or AFTER
Prevent recursion
Know when not to use a trigger
Scope of this micro-course
This micro-course targets one focused skill. It does not replace a complete path or server administration training.
Prerequisites
Constraints
INSERT, UPDATE, DELETE
Transactions
Project and practice
10 verified exercises are available. Validation is based on the programme exercises.
Focused path
Your programme, in order.
Content remains centralised: completing an activity here also updates the path that contains it.
Micro-lessons
01
The problem to solve before syntax
A rule copied in several applications diverges. A function can centralize it close to the data, but logic that is too opaque or very coupled becomes difficult to test and deploy.
A function returns a value or a set and can be used in a query depending on its properties. A procedure is called with CALL and is suitable for an imperative operation; exact capacities depend on the DBMS.
A business wants to keep every old price, regardless of which tool makes the change. A trigger can guarantee this logging, but it adds implicit execution to each event.
The trigger function receives OLD and NEW. AFTER is suitable for auditing a successful change; BEFORE can adjust or reject the line. Limit triggering with WHEN and keep the function small.
Test real change, unchanged value, undo transaction and massive update. Control recursion when the trigger writes to a table affected by another trigger.
Create a billing business function 2 training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Create a logistics business function 3 · training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Create an HR business function 4 · training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.
Create an audit business function 5 training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this audit log.
Logging an invoicing modification 2 · training scenario
Retain the value before and after a change is actually executed. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Historicize a logistics modification 3 · training scenario
Retain the value before and after a change is actually executed. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Retain the value before and after a change is actually executed. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.
Logging an audit modification 5 · training scenario
Retain the value before and after a change is actually executed. You must produce the result, check it on the source data and explain what it allows you to decide in this audit log.