Thread: When use triggers?

On 05/16/2018 03:19 PM, hmidi slim wrote:
> HI,
> 
> I'm working on a microservice application and I avoid using triggers 
> because they will not be easy to maintain and need an experimented 
> person in database administration to manage them. So I prefer to manage 
> the work in the application using ORM and javascript.
> However I want to get some opinions and advices about using triggers: 
> when should I use them? How to manage them when there are some problems?

Two benefits triggers and their associated functions offer, with the 
understanding these are general statements:

1) Are in the database so tasks that always need happen do not need to 
be replicated across the various interfaces that touch the database.

2) Also since they run on the server the data does not necessarily cross 
a network, so less network bandwidth and better performance.

An example of a use case is table auditing. If you need to track what is 
being INSERTed/UPDATEd/DELETEd in a table stick an audit 
trigger/function on the table and push the information to an audit table.

As to managing, they are like any other code. I keep my schema code in 
scripts under version control and deploy them from there. I use 
Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. 
I use a dev database to test and troubleshoot triggers and functions.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 05/16/2018 03:19 PM, hmidi slim wrote:
>>
>> HI,
>>
>> I'm working on a microservice application and I avoid using triggers
>> because they will not be easy to maintain and need an experimented person in
>> database administration to manage them. So I prefer to manage the work in
>> the application using ORM and javascript.
>> However I want to get some opinions and advices about using triggers: when
>> should I use them? How to manage them when there are some problems?
>
>
> Two benefits triggers and their associated functions offer, with the
> understanding these are general statements:
>
> 1) Are in the database so tasks that always need happen do not need to be
> replicated across the various interfaces that touch the database.
>
> 2) Also since they run on the server the data does not necessarily cross a
> network, so less network bandwidth and better performance.
>
> An example of a use case is table auditing. If you need to track what is
> being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
> the table and push the information to an audit table.
>
> As to managing, they are like any other code. I keep my schema code in
> scripts under version control and deploy them from there. I use
> Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
> use a dev database to test and troubleshoot triggers and functions.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

The only appropriate use for triggers that I've ever found was for
auditing changes to tables.

It can be quite trivial for simple cases: just use an update trigger
and write all of the "deleted" (e.g., before the update)
values/columns that you want to track to your audit table.  Each row
in the audit represents the previous state before changes were made.
It's so trivial that you could write a sql script to generate a
simplest-case audit table and audit trigger for any table where you
don't need any custom behavior in the audit logic

As for management, you could also have a build or deployment task that
updates audit tables and the triggers as columns are added (or perhaps
removed) from their related tables (e.g., semi-automatic management).

To keep them lightweight, my typical audit table is insert-only (other
grants are removed) with a primary key, no foreign keys, and a single
nonunique index on the main table's primary key if the audits related
to a main-table row need to be viewable by an end user or something
similar.

It's kind of interesting that you speak of microservices and triggers.
If you go "all in" with microservices, the typical approach is to use
event sourcing and CQRS and then all of your writes __are__ your
audit.

~Mike Stephenson

hmidi slim <hmidi.slim2@gmail.com> writes:

> HI,
>
> I'm working on a microservice application and I avoid using triggers
> because they will not be easy to maintain and need an experimented person
> in database administration to manage them. So I prefer to manage the work
> in the application using ORM and javascript.
> However I want to get some opinions and advices about using triggers: when
> should I use them? How to manage them when there are some problems?

I think triggers are generally best avoided. They do have a purpose, but
like regular expressions and Lisp style macros, they are abused more
often than used appropriately. When used correctly, they can help to
ensure your code is robust, clear and easy to maintain. 

The big issue with triggers is that they are really a side effect of
some other action. As such, they are obscure, easily missed, difficult
to debug and often frustrating to maintain.

In nearly 30 years of working with different databases, I've rarely
found triggers necessary. As mentioned by others in this thread, they
can be useful when you need low level auditing and like all guidelines,
there are always exceptions, but in general, they should usually be the
last choice, not the first.

Database functions on the other hand are extremely useful and probably
something more developers should take advantage of. There are far too
many applications out there which are doing things within external
application code which could be handled far more efficiently and
consistently as a database function. The challenge is in getting the
right balance.

My rule of thumb is to develop under the assumption that someone else
will have this dumped on them to maintain. I want the code to be as easy
to understand and follow as possible and I want to make it as easy to
make changes and test those changes as possible. Therefore I prefer my
code to consist of simple units of functionality which can be tested in
isolation and have a purpose which can be understood without requiring a
knowledge of hidden actions or unexpected side effects. If a function
cannot be viewed in a single screen, it is probably too big and trying
to do too many different things which should be broken up into smaller
functions. 

regards,

Tim


-- 
Tim Cross

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 05/16/2018 03:19 PM, hmidi slim wrote:

HI,

I'm working on a microservice application and I avoid using triggers
because they will not be easy to maintain and need an experimented person in
database administration to manage them. ...

Two benefits triggers and their associated functions offer, with the
understanding these are general statements:

1) Are in the database so tasks that always need happen do not need to be
replicated across the various interfaces that touch the database.

2) Also since they run on the server the data does not necessarily cross a
network, so less network bandwidth and better performance.

An example of a use case is table auditing. If you need to track what is
being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
the table and push the information to an audit table.

As to managing, they are like any other code. I keep my schema code in
scripts under version control and deploy them from there. I use
Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
use a dev database to test and troubleshoot triggers and functions.

The only appropriate use for triggers that I've ever found was for
auditing changes to tables. ...

A great use case for triggers and stored functions is data validation or cleaning. Sure, this could be done in the end-user application ... or, that is, in *all* end-user applications if you don't mind duplicating algorithmic implementations and you trust the applications to do it properly and the same way every time and non-maliciously.

Triggering a before-insert function allows for a single implementation to be used consistently for all inserts.

Also, while appropriately limiting access privileges for end-users, triggers can invoke functions that do stuff at a higher privilege level safely, such as, for example, creating data base roles for new users.

Here's a complicated but cool example:

First, there is a publicly-exposed but non-data-leaking view that allows unprivileged users to initiate inserts for account creation (and by unprivileged I mean really unprivileged, that is, end users that don't even have an associated database login role when they do the insert):


CREATE OR REPLACE VIEW public.fairian AS
 SELECT NULL::name AS fairian_name,
    NULL::name AS passwd,
    NULL::name AS email_address;

GRANT USAGE ON SCHEMA public TO public;
GRANT SELECT, INSERT ON TABLE public.fairian TO public;


A rule redirects inserts on the public dummy view to a protected intermediate view (i.e., a view contained in non-publicly-visible schema) on which new and unprivileged users do not have read, write, or update privileges, but since relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule, this actually works:

REVOKE ALL ON schema protected  FROM public;

CREATE OR REPLACE RULE fairian_iir AS
    ON INSERT TO public.fairian DO INSTEAD 
    INSERT INTO protected.fairian (fairian_name, passwd, email_address)
    VALUES (new.fairian_name, new.passwd, new.email_address);


The redirected insert triggers a function that drills the insert down one level further to the actual table that is visible only to the data base owner:


CREATE TRIGGER fairian_iit
  INSTEAD OF INSERT
  ON protected.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE protected.fairian_iit();

CREATE OR REPLACE FUNCTION protected.fairian_iit()
  RETURNS trigger AS
$BODY$
BEGIN
    -- Note, the password is not actually stored but there is some
    -- validation ... look at the private trigger function

    INSERT INTO private.fairian (fairian_name, passwd, email_address)
        VALUES (new.fairian_name, new.passwd, new.email_address);

  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


When the insert is finally pushed all the way down to the actual table in the (non-publicly visible) private schema, there's a trigger function which prevents account name collisions by serializing inserts, that ensures the credentials meet certain requirements such as containing no white space and starting with a character, and (although omitted below) sanitizes against SQL injection:


REVOKE ALL ON schema private  FROM public;

CREATE TRIGGER fairian_bit
  BEFORE INSERT
  ON private.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE private.fairian_bit();


CREATE OR REPLACE FUNCTION private.fairian_bit()
  RETURNS trigger AS
$BODY$
    DECLARE
      _fairwinds private.fairwinds;
    BEGIN
      -- Serializes account creation
      select * into _fairwinds from private.fairwinds for update;

    -- Deny white space characters
   
    if position(' ' in new.fairian_name) > 0 then
        raise exception 'Fairian name may not contain spaces.';
    end if;
    if position(' ' in new.passwd) > 0 then
        raise exception 'Password may not contain spaces.';
    end if;
    if position(' ' in new.email_address) > 0 then
        raise exception 'E-mail address may not contain spaces.';
    end if;

    if not (select new.fairian_name similar to '[a-zA-Z]%') then
        RAISE EXCEPTION 'Fairian name must begin with a letter.';
    end if;

    --
    -- Anti-SQL-injection cleaning code omitted here
    -- 
      
    EXECUTE 'CREATE ROLE ' || new.fairian_name || ' WITH
        LOGIN
        INHERIT
        NOSUPERUSER
        NOCREATEDB
        NOCREATEROLE
        ENCRYPTED
        PASSWORD ' || quote_literal(new.passwd) || ' IN GROUP fairwinds';

    EXECUTE 'ALTER USER ' || new.fairian_name || ' SET search_path = privileged,public,pg_temp;';

    -- Do not store the plaintext password
   
    NEW.passwd = NULL;
        RETURN NEW;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


Whew!