agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Mike Baroukh <mbaroukh@i-panema.fr>
To: craig.may@s2.enthdimension.com.au
To: pgsql-sql@postgresql.org
Subject: Re: Trigger
Date: Wed, 6 Sep 2000 12:24:15 +0200
Message-ID: <028b01c017ec$9bce6790$0102000a@ipanema.com> (raw)
References: <20000906202707.A4767@s2.enthdimension.com.au>


There is a sample in postgres documentation. (See below).
the only problem is for using langage plpgsql.
If it is not understand by your database, you must use command

createlang plpgsql dbname

as the owner of the database.

CREATE TABLE emp (
        empname text,
        salary int4,
        last_date datetime,
        last_user name);

    CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
        BEGIN
            -- Check that empname and salary are given
            IF NEW.empname ISNULL THEN
                RAISE EXCEPTION ''empname cannot be NULL value'';
            END IF;
            IF NEW.salary ISNULL THEN
                RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
            END IF;

            -- Who works for us when she must pay for?
            IF NEW.salary < 0 THEN
                RAISE EXCEPTION ''% cannot have a negative salary'',
NEW.empname;
            END IF;

            -- Remember who changed the payroll when
            NEW.last_date := ''now'';
            NEW.last_user := getpgusername();
            RETURN NEW;
        END;
    ' LANGUAGE 'plpgsql';

    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
        FOR EACH ROW EXECUTE PROCEDURE emp_stamp();




----- Original Message -----
From: Craig May <craig.may@s2.enthdimension.com.au>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, September 06, 2000 10:27 PM
Subject: [SQL] Trigger


>
> Could someone send me a quick example of a trigger.
>

view thread (14+ messages)  latest in thread

Message-ID: <028b01c017ec$9bce6790$0102000a@ipanema.com>
Permalink:  ../028b01c017ec$9bce6790$0102000a@ipanema.com/
Also on:    postgresql.org/message-id/028b01c017ec$9bce6790$0102000a@ipanema.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: mbaroukh@i-panema.fr, craig.may@s2.enthdimension.com.au
  Subject: Re: Trigger
  In-Reply-To: <028b01c017ec$9bce6790$0102000a@ipanema.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox