Sql Triggers Can Be Used When the Dbms Receives a ________ Request.

I was 12 years old when I start heard about SQL triggers. My blood brother, Jonathan, had just begun his software career at a startup. Jonathan came home one day frustrated past a database full of convoluted SQL triggers.

With my only programming experience being recreating my favorite video game in VB6, I had little consolation to offer.

Fast forrad sixteen years, and now I can see from my brother'southward perspective. In the world of open source start-up style full-stack evolution (think Django, Runway, Javascript, PHP, MySQL, Postgres..), ORMs are very pop and features like SQL triggers are far less conventional.

But there is still value with SQL triggers. During my time working on custom ERP-similar software, SQL triggers were an invaluable tool. When building highly data oriented software, particularly when the data is of financial nature and accuracy is of high demand, you're more likely to run into data existence manipulated at a lower level, in a more than direct way.

This article contains all the data I wish I could have shared with my brother on how to effectively use SQL triggers.

Table of contents

• What is a SQL Trigger?
• How to Create a SQL Trigger - Syntax
• Postgres Trigger Case 1: Creating a Time Clock
• Postgres Trigger Example two: Creating an Audit Table
• Additional Considerations for Triggers

What is a SQL Trigger?

SQL Triggers, also called Database Triggers, let y'all to tell your SQL engine (for these examples, Postgres) to run a piece of lawmaking when an issue happens, or even earlier the issue.

In Postgres, you delineate the lawmaking to run by creating a function whose return type is trigger. In another engines like MySQL, the code block is a office of and within the trigger.

Earlier I discuss what the different issue types are and the specific syntax for creating a trigger, why would you want to utilise a database trigger?

Advantages of using SQL Triggers

Maintaining data integrity

Database triggers have a diversity of uses and are an splendid tool to marshal strict data integrity. Alternate solutions similar Django's model hooks may fail if you accept other application servers or users accessing the database who aren't aware of the specific business logic coded in your application.

Separating business logic

Coding critical business organization logic within the application code also presents problems when the business logic is updated. If you had a business requirement to multiply an incoming number by 10, and you wanted to revise this logic to multiply the number past xx, irresolute the logic in SQL would guarantee that every piece of data from that exact deploy time on would be affected by the new logic.

The SQL server acts as a single point of truth. If the logic is implemented on multiple application servers, you can no longer expect a clean, definitive change in behavior.

Diminutive transactions

Natural atomicity is another desirable characteristic bundled with triggers. Since the event and the trigger function are all part of one atomic transaction, you know with accented certainty that the trigger will burn if the event fires. They are as 1, in perfect SQL marriage.

How to Create a SQL Trigger - Postgres Syntax

Hither are the components to creating a trigger for your database:

1. Trigger Event Type
2. Before or After the event
3. Result of the Trigger

SQL Trigger Beefcake

Trigger event types

Database triggers volition monitor tables for specific events. Hither are some examples of dissimilar events that can activate a trigger:

• Changing data: INSERT, UPDATE, DELETE

A database trigger can also list more than one of these events.

If UPDATE was ane of the listed events, you can pass in a list of columns that should activate the trigger. If you lot don't include this listing, updating whatever cavalcade will activate it.

Trigger Before or After

A trigger can run either BEFORE or AFTER an effect.

If you want to block an issue like an INSERT, y'all will want to run Earlier. If you need to exist sure the event really is going to occur, Later on is ideal.

Effect of the trigger

A trigger can run either per row, or per statement. Permit'due south say you run a single UPDATE statement that changes 5 rows in a table.

If yous specify FOR EACH ROW in the trigger, so the trigger will run 5 times. If yous specified FOR EACH Argument, and then it would but run one time.

And of course we can't forget the actual code to run when the trigger is activated. In Postgres, is placed in a role and separated from the trigger. Separating the trigger from the code it runs creates cleaner code and allows multiple triggers to execute the aforementioned code.

Postgres Trigger Example #1: Creating a Time Clock

A time clock records when an employee comes and leaves from work and calculates his/her full hours worked. Permit's create an instance time clock and await at how we can employ triggers to prevent employees from inputting invalid data.

Setting up the DB schema

The design of this schema treats each punch in and out as split events. Each upshot is a row in the time_punch table. Alternatively, you could also make each employee" shift" an event and store both the punch in and dial out time in ane row.

Subscribe to our newsletter to get notified of a time to come post where I'll do a deep dive on how to ascertain a strong database schema.

For this example I've gone ahead and defined the schema for our tables. The lawmaking below creates an employee and time_punch table and inserts some time punch data for a new employee, Bear.

          create table employee (   id series principal key,   username varchar );  create tabular array time_punch (   id serial principal primal,   employee_id int not null references employee(id),   is_out_punch boolean non null default false,   punch_time timestamp non zip default now() );  insert into employee (username) values ('Bear'); insert into time_punch (employee_id, is_out_punch, punch_time) values (1, false, '2020-01-01 x:00:00'), (1, truthful, '2020-01-01 11:xxx:00');                  

Bear has clocked in at 10:00am and out at 11:30am (long day at work). Let's write a SQL query to calculate how long Bear has worked.

Take a pause here and think about how y'all would solve this given our schema and using only SQL.

---

Using SQL to calculate time worked

The solution I decided on looks at each "out" punch and matches it with its preceding "in" punch.

          select tp1.punch_time - tp2.punch_time as time_worked from time_punch tp1 join time_punch tp2 on tp2.id = ( 	select tps.id 	from time_punch tps 	where tps.id < tp1.id 	and tps.employee_id = tp1.employee_id 	and not tps.is_out_punch 	order by tps.id desc limit one ) where tp1.employee_id = 1 and tp1.is_out_punch        

          time_worked -------------  01:xxx:00 (1 row)        

In this query I select all the out punches, then I join them to the closest preceding "in" punch. Subtract the timestamps, and we get how many hours Conduct worked for each shift!

One of the issues with this schema is that information technology is possible for y'all to insert several "in" or "out" punches in a row. With the query nosotros've created, this would introduce ambiguities that could pb to inaccurate calculations and employees getting paid more than or less than they should.

SQL INSERT BEFORE trigger example - preserving information integrity

We need something to prevent the in/out pattern from being interrupted. Unfortunately bank check constraints only look at the row being inserted or updated and cannot gene in data from other rows.

This is a perfect state of affairs to utilise a database trigger!

Let's create a trigger to prevent an INSERT consequence that would break our pattern. Beginning we'll create the "trigger office". This office is what the trigger will execute when the result type is detected.

A trigger role is created like a regular Postgres office, except that information technology returns a trigger.

          create or replace part fn_check_time_punch() returns trigger as $psql$   begin     if new.is_out_punch = (       select tps.is_out_punch       from time_punch tps       where tps.employee_id = new.employee_id       club past tps.id desc limit i     ) then       return null;     finish if;     return new;   end; $psql$ linguistic communication plpgsql;                  

The new keyword represents the values of the row that is to be inserted. Information technology also is the object yous can render to permit the insert to continue. Alternatively, when nada is returned this stops the insertion.

This query finds the time_punch before and ensures its in/out value is not the same every bit what's beingness inserted. If the values are the same, then the trigger returns null and the time_punch is not recorded. Otherwise, the trigger returns new and the insert statement is immune to continue.

Now we'll link the function as a trigger to the time_punch table. Earlier is crucial here. If we ran this trigger every bit an AFTER trigger it would run besides late to stop the insertion.

          create trigger check_time_punch before insert on time_punch for each row execute procedure fn_check_time_punch();                  

Let's endeavour to insert another "out" punch:

          insert into time_punch (employee_id, is_out_punch, punch_time) values (i, true, '2020-01-01 xiii:00:00');        

          Output: INSERT 0 0        

Nosotros tin see from the output that the trigger prevented the insertion of 2 subsequent out punches for the same employee.

It is also possible to heighten an exception from the trigger so that your application (or person running the SQL query) receives a failure detect instead of the insert count simply being 0.

Postgres Trigger Example #ii: Creating an Audit Table

Accurately storing employee punch data is critical for businesses. This type of data often ends up direct translating to an employee's salary, and on the other finish, a company's payroll cost.

Because of the importance of this information, let's say the visitor wants to be able to recreate all the historical states of the table in the event that an irregularity is discovered.

An audit table accomplishes this by keeping rail of every modify to a tabular array. When a row is updated on the primary table, a row will be inserted into the audit tabular array recording its past land.

I will use our time_punch table to demonstrate how to create an automatically updating audit table using triggers.

Create the audit table

At that place are several ways to keep an audit or history table. Let'southward create a separate table that volition shop the past states of time_punch.

          create tabular array time_punch_audit (   id serial primary key,   change_time timestamp not null default now(),   change_employee_id int not null references employee(id),   time_punch_id int not zippo references time_punch(id),   punch_time timestamp not null );                  

This table stores:

• Time the dial was updated
• Employee who updated it
• ID of the punch that was changed
• Punch time before the punch was updated

Before nosotros create our trigger, we first need to add a change_employee_id column to our time_punch tabular array. This way the trigger will know which employee made every modify to the time_punch table.

          change table time_punch add column change_employee_id int null references employee(id);                  

(An alternative solution without calculation any columns to time_punch could be to revoke update permission on this table, and strength users of this database to use a custom function like update_time_punch(id, change_user_id, ...))

SQL UPDATE AFTER trigger example - inserting data

After an update happens to our time_punch table, this trigger runs and stores the Old punch fourth dimension value in our audit table.

          create or supercede function fn_change_time_punch_audit() returns trigger as $psql$   begin     insert into time_punch_audit (change_time, change_employee_id, time_punch_id, punch_time)     values     	(now(), new.change_employee_id, new.id, onetime.punch_time);     return new;   terminate; $psql$ linguistic communication plpgsql;                  

          create trigger change_time_punch_audit after update on time_punch for each row execute procedure fn_change_time_punch_audit();        

The NOW() function returns the current appointment and time from the SQL server's perspective. If this were linked up to an bodily application, you lot'd desire to consider passing in the exact fourth dimension the user really fabricated the asking to avoid discrepancies from latency.

During an update trigger, the NEW object represents what values the row will contain if the update is successful. You tin use a trigger to "intercept" an insert or update by simply assigning your own values to the NEW object. The Erstwhile object contains the row'due south values pre-update.

Let's see if it works! I have inserted a second user named Daniel who volition be the editor of Conduct'southward time punches.

          select punch_time from time_punch where id=ii;        

                      punch_time       ---------------------  2020-01-01 11:thirty:00 (ane row)                  

I'k going to run the query below twice to simulate two edits that increment the time past five minutes.

          update time_punch set punch_time = punch_time + interval '5 minute', change_employee_id = 2 where id = 2;                  

And hither is the audit table, reflecting the past punch times:

                      change_time         | username |     punch_time       ----------------------------+----------+---------------------  2021-01-06 20:10:56.44116  | Daniel   | 2020-01-01 11:35:00  2021-01-06 twenty:10:55.133855 | Daniel   | 2020-01-01 11:thirty:00                  

Additional Considerations for Triggers

There'south a few things to be wary of with database triggers:

1. Maintaining triggers over fourth dimension
2. Connected trigger logic
3. Developer expertise

Maintaining triggers over time

Business concern logic in application lawmaking is naturally documented as it changes over time past way of git or another source command organisation. It'south piece of cake for a developer to see some logic in a codebase and do a quick git log and come across a list of changes.

Managing changes over time with SQL triggers and functions is more than complicated, less standardized, and requires more thought and planning.

Connected trigger logic

Triggers can as well prepare off other triggers, quickly complicating the results of what could appear to be an innocent INSERT or UPDATE. This risk is besides inherent in application lawmaking with side furnishings.

Developer expertise

Sensation of triggers is also far lower amidst some developer circles so introducing them increases the investment in grooming that will be required for new developers to successfully piece of work on your projection.

SQL tin can initially be a clumsy and frustrating linguistic communication to learn every bit many of the patterns yous learn to build a query are "within out" from how you'd extract information in a procedural language.

Using Triggers to Level Up Your SQL Game

I promise that these examples have helped yous develop a better understanding of database triggers. I accept had swell successes using triggers for data problems where traceability, consistency, and accuracy are paramount.

Making the decision to introduce triggers to your awarding is ane that should be fabricated after careful thought - and I for one promise you go an opportunity to implement and explore 1 of SQL's most fun and intriguing features!

If you're interested in continuing to amend your SQL skills, consider checking out Arctype. Arctype is a modern SQL editor designed to simplify the process of working with databases and SQL. Bring together our growing customs and download Arctype today.

CodeProject

dodgesirle1990.blogspot.com

Source: https://arctype.com/blog/learn-sql-triggers/

Share this post