trigger-01.txt

testdb=> create table account_log ( account_id integer, change integer, timestamp timestamp );
CREATE TABLE
testdb=> alter table account_log drop column timestamp;
ALTER TABLE
testdb=> alter table account_log create column datetime timestamp;
FEHLER:  Syntaxfehler bei »create«
ZEILE 1: alter table account_log create column datetime timestamp;
                                 ^
testdb=> alter table account_log add column datetime timestamp;
ALTER TABLE
testdb=> \d account_log
                             Tabelle »public.account_log«
   Spalte   |             Typ             | Sortierfolge | NULL erlaubt? | Vorgabewert
------------+-----------------------------+--------------+---------------+-------------
 account_id | integer                     |              |               |
 change     | integer                     |              |               |
 datetime   | timestamp without time zone |              |               |

testdb=> create trigger after_update_account after update on account for each row begin if new.balance <> old.balance then insert into account_log ( account_id, change, datetime ) values ( new.id, new.balance - old.balance, now () ) end;
FEHLER:  Syntaxfehler bei »begin«
ZEILE 1: ...date_account after update on account for each row begin if n...
                                                              ^
testdb=> create procedure log_change (account_id integer, change integer) language sql as 'INSERT INTO account_log (account_id, change, datetime) values (account_id, change, now ())';   CREATE PROCEDURE
testdb=> create trigger after_update_account after update on account for each row log_change (old.account_id, new.balance - old. balance);                                                FEHLER:  Syntaxfehler bei »log_change«
ZEILE 1: ...date_account after update on account for each row log_change...
                                                              ^
testdb=> create trigger after_update_account after update on account for each row call log_change (old.account_id, new.balance - old. balance);
FEHLER:  Syntaxfehler bei »call«
ZEILE 1: ...date_account after update on account for each row call log_c...
                                                              ^
testdb=> drop procedure log_change;
DROP PROCEDURE
testdb=> create function log_change (account_id integer, change integer) returns trigger language sql as 'INSERT INTO account_log (account_id, change, datetime) values (account_id, change, now ())';
FEHLER:  SQL-Funktionen können keinen Rückgabetyp »trigger« haben
testdb=> create function log_change (account_id integer, change integer) returns trigger language plpgsql as 'BEGIN; INSERT INTO account_log (account_id, change, datetime) values (account_id, change, now ()); END;';
FEHLER:  Triggerfunktionen können keine deklarierten Argumente haben
TIP:  Auf die Argumente des Triggers kann stattdessen über TG_NARGS und TG_ARGV zugegriffen werden.
KONTEXT:  Kompilierung der PL/pgSQL-Funktion »log_change« nahe Zeile 1
testdb=> create function log_change () returns trigger language plpgsql as 'BEGIN; INSERT INTO account_log (account_id, change, datetime) values (account_id, change, now ()); END;';     FEHLER:  Syntaxfehler bei »;«
ZEILE 1: ...ange () returns trigger language plpgsql as 'BEGIN; INSERT I...
                                                              ^
testdb=> create function log_change () returns trigger language plpgsql as $$                testdb$> if new.balance <> old.balance then
testdb$>   insert into log

testdb$>   insert into log_change (account_id, change, datetime) values (old.id, new.balance - old.balance, now ());
testdb$> end if;
testdb$> return new;
testdb$> end;
testdb$> $$
testdb-> ;
FEHLER:  Syntaxfehler bei »if«
ZEILE 2: if new.balance <> old.balance then
         ^
testdb=> create function log_change () returns trigger language plpgsql as $$
begin
  if new.balance <> old.balance then
    insert into log_change (account_id, change, datetime)
           values (old.id, new.balance - old.balance, now ());
  end if;
  return new;
end;
$$
;
CREATE FUNCTION
testdb=> create trigger after_update_account after update on account for each row execute procedure log_change ();
CREATE TRIGGER                                                                               testdb=>