Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Working with Triggers in PostgreSQL

In this article we will learn in practice the use of triggers in PostgreSQL 9.4 database, and learn how to use the rules.

As the database grows, so does its complexity, and because of this, it is difficult to implement or even to troubleshoot issues that the database must perform before or after a specific event.

In PostgreSQL we have at our disposal the rules, which are tasks that can be performed in conjunction with the triggers responsible for their implementation. We can use the rules to implement the Views in PostgreSQL, which are virtual tables that help in the data clustering present in one or more tables. In this article we will learn how to work with the triggers and their relation to the Rules and Views.

An overview of the triggers

Triggers, in terms of database operations, are carried out spontaneously to specific events. When treating events, these may be both an UPDATE as an INSERT, or even anDELETE. So we can define certain operations to be performed whenever the event occurs.

When referring to an operation with a trigger, this function is known to trigger or trigger function. Remember that trigger and trigger function are two different things, where the first can be created using the CREATE TRIGGER, while the latter is defined by CREATE FUNCTION. Generally speaking, with triggers we define which task to run, and with the function triggers we define how this task is performed.

To have a lot of access to the database by multiple applications, the use of triggers is very useful, and with that, we can maintain the integrity of complex data, and we can track changes or logging every change occurred in present data in a table.

To begin, we will create, in an abstract way, a trigger function and a trigger, as we see presented in Listings 1 and 2. Triggers functions can be defined in the PostgreSQL compatible languages such as PL/pgSQL, PL/Python, PL/Java among others. For this article we use the PL/pgSQL.

Listing 1. Creating the structure of a trigger function.

CREATE OR REPLACE FUNCTION trigger_function_name
RETURNS trigger AS $FunctionExample$
BEGIN
/* Here we define our codes.*/
RETURN NEW;
END;
$FunctionExample$ LANGUAGE plpgsql;

Listing 2. Syntax of a simple trigger.

CREATE [ CONSTRAINT ] TRIGGER NAME { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  ON table_NAME
  [ FROM referenced_table_NAME ]
  [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE PROCEDURE function_NAME ( arguments )
-- When an event is declared with:
  INSERT
  UPDATE [ OF column_NAME [, ... ] ]
  DELETE
  TRUNCATE

Note that a trigger function is actually a function, but with the difference that it takes no arguments, but a special data structure called TriggerData. Please note that your return type is the trigger, where it is called automatically at the time of occurrence of the events (which can be INSERT, UPDATE, DELETE, or TRUNCATE). With PostgreSQL, two types of trigger are available: Line trigger level (row-level Trigger) and Trigger instruction level (statement level trigger). Both are specified with the use of clauses FOR EACH ROW (row trigger level) and FOR EACH STATEMENT, respectively. Use of them can be defined according to the number of times that the trigger should be performed. For example, if an UPDATE statement is executed, and this affects six lines, a row-level trigger will be performed six times, while the trigger instruction level will be called only once per SQL statement.

When using triggers we can connect both tables and views, so that the triggers are executed for tables in two situations: BEFORE and AFTER to any of the DML (INSERT, UPDATE, DELETE), and also enable their implementation using the TRUNCATE statement.

When we have the trigger set to the instruction we can use the DML's for the Views. The triggers will be fired before or after the DML statements, but they can be set only at instruction level. But, when we use INSTEAD OF in the DML statements, we can execute them only at line level.

For all other parameters, we have the NAME that is used to assign a name for the trigger, which must be distinguished from other triggers created for the same table. The table_NAME statement shows the table name in use.

As for the events, these can be INSERT, UPDATE, DELETE, or TRUNCATE, which specify the event that will fire the trigger.

The condition expression is a Boolean expression that determines whether the trigger function will be executed. If the WHEN condition is specified, the function will be called if the condition evaluates to true. In addition, it can be referred to the columns that contain the old values and want to spend the new values. For this are used the instructions OLD.column_NAME or NEW.column_NAME respectively. Remember that function_names are functions provided by users.

Finally, we have the arguments, which are optional argument lists separated by commas that can be provided to the function when the trigger is executed.

To demonstrate the procedure for establishing triggers and their use we will create some simple examples.

For this, we will create a new database, which will call DbTeste and then we will create a table Employees that contains the fields in Listing 3. In our example we want to keep updated all the records added to a possible audit_employees.

Listing 3. Creating Employees test table.

  CREATE TABLE employee
  (
    name character varying(100) NOT NULL,
    email character varying(200) NOT NULL,
    phone character(14) NOT NULL,
    profession character varying(150) NOT NULL,
    address character varying(100) NOT NULL,
    salary real
  ) 

After the establishment of our main table we will create a new table with the name of audit_employees_employees, which is responsible for maintaining the history of changes made in the records, as we can see in Listing 4.

Listing 4. Creation ofaudit_employees_employeestable.

  CREATE TABLE audit_employees_employees (
      emp_code INT NOT NULL,       update_date TEXT NOT NULL   ); 

Note that there are only two fields: the Employee ID and the date of modification/creation of the record, you will receive a date from the Timestamp format at the time the record is created in the Employees table.

To give follow-up to our tests we will enter some data, as we can see in Listing 5.

Listing 5. Entering data in the employees table.

  INSERT INTO EMPLOYEES (code, name, email, phone, profession, endereco, salary) VALUES (1, 'Ed Sharp', 'ed.sharp@gmail.com', '(81)997402800', 'Web Developer', 2000.00);
  INSERT INTO EMPLOYEES (code, name, email, phone, profession, endereco, salary) VALUES (2, 'Mary Katy', 'mkaty@gmail.com', '(81)997402844', 'Systems Engeneering', 'Test Street', 6000.00);
  INSERT INTO EMPLOYEES (code, name, email, phone, profession, endereco, salary) VALUES (3, 'Caroline Waly', 'carol@gmail.com', '(81)997402800', 'Tests Analyst', 'Test Street', 2500.00);
  INSERT INTO EMPLOYEES (code, name, email, phone, profession, endereco, salary) VALUES (4, 'John Spred', 'johnsp@gmail.com', '(81)997401654', 'Financial Analyst', 'Test Street', 8000.00);
  INSERT INTO EMPLOYEES (code, name, email, phone, profession, endereco, salary) VALUES (5, 'Mary Olsen', 'maryols@gmail.com', '(81)997407845', 'Secretary', 'Test Street', 1800.00); 

The triggers receive functions through a separate input, a TriggerData structure, which has a set of local variables that we use in our triggers functions. Among the variables present in this structure we have the OLD and NEW variables, and other that begin with the prefix TG_ as TG_TABLE_NAME.

The variable NEW is oftype RECORD and contains a new line to be stored based on INSERT/UPDATE triggers of the line level.

The variable OLD, in the other hand, is also the RECORD type and stores the old line when used with the UPDATE/DELETE commands in line triggers.

After the creation of our tables we will define a trigger function, which will call employee_log_func, and will be responsible for recording changes made in audit_employee table after an INSERT operation in the Employees table, as presented by the Listing 6.

Listing 6. Creation of the trigger function employee_log_func.

  CREATE OR REPLACE FUNCTION employee_log_func()
  RETURNS trigger AS $test_trigger$
  BEGIN
  INSERT INTO audit_employees_employees
  (log_id, creation_date)
  VALUES
  (new.code_func, current_timestamp);
  RETURN NEW;
  END;
  $test_trigger$ LANGUAGE plpgsql; 

With our function created, now we can define our trigger and then associate the employee table, as we can see in Listing 7.

Listing 7. Creation of log_trigger trigger.

  CREATE TRIGGER log_trigger
  AFTER INSERT ON employee
  FOR EACH ROW
  EXECUTE PROCEDURE employee_log_func();

When we insert a new record in our employee table, we can see that a new record was also set in audit_employees table.

For an example a little more complex we will create a trigger containing the three DML's operations contained in the same trigger function. For this, initially we will have a change in our audit_employees table, which will add a new realized_operation column to store the name of the operation performed.

Before we proceed, we will delete the table audit_employees using the DROP command, as seen below:

Drop table audit_employees cascade;

After deletion of the table, we will create it back, but with the following columns in Listing 8.

Listing 8. Recreating audit_employees table.

  CREATE TABLE audit_employees
  (
  log_id INT NOT NULL,
  creation_date TEXT NOT NULL,
  realized_operation CHARACTER VARYING
  ); 

Conclusion

With this we finish this article, where we had a more practical approach to use of triggers and a little about the rules that make the fastest implementations compared the triggers, in some cases.

We hope you enjoyed. See you in the next!



Web developer and passioned for web design, SEO and front end technologies.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login