Converting a SQL Server trigger to PostgreSQL trigger problems with the trigger function

Question

I am in the middle of converting an existing SQL Server 2005 DB into a PostgreSQL 9.0 DB.

Everything works fine until now. I want to translate a SQL trigger into PostgreSQL but I have a problem with the trigger function.

I don't know how to implement the temp table inserted in the PostgreSQL syntax. In SQL Server the inserted table exists but not in PostgreSQL. Any ideas?

My code (PostgreSQL):

CREATE OR REPLACE FUNCTION func_co_insert()

  RETURNS trigger AS

$BODY$begin

  declare

  aa bigint;

begin

  select aa = co_id from inserted;

  update com03 set co_creationdate = CURRENT_TIMESTAMP,

  co_creationby = USER where co_id = aa;

end;

end;

---

Here the code of the trigger body of the SQL Server 2005 code

begin

    declare @aa bigint;

    select @aa = se_id from inserted;

    update server set se_creationdate = CURRENT_TIMESTAMP , se_creationby = USER where se_id = @aa;

end;

thanks

Chris

Answer 1

The default in PostgreSQL is a row level trigger (as opposed to SQL Server where it's a statement level trigger), so there is no need for an "inserted" table to select from.

The new and old values can be accessed using the keyword new and old (old does not exist for an insert trigger).

In your case the statement would simply be:

  update com03 
       set co_creationdate = CURRENT_TIMESTAMP,
       co_creationby = CURRENT_USER
  where co_id = new.co_id;

No need to "select from inserted".

This assumes the trigger is not firing for the table com03. If your trigger fires for com03 (which you didn't tell us), then it' even easier:

  new.co_creationdate := current_timestamp;
  new.co_creationby := current_user;

For details please refer to the manual: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

That page also contains an example which does exactly what you are trying to achieve