CODEWITHSUNDEEP
sqldatabasepostgresqltriggersplpgsql
Pradeep
Pradeep

Reputation: 1223

Update trigger not firing in plpgsql

I have an update trigger and insert trigger on a table nums with two columns namely name,number.Whenever data is updated or inserted then these triggers are executed.

CREATE OR REPLACE FUNCTION add_log()
RETURNS trigger AS
$BODY$
DECLARE
    account_type varchar;
BEGIN   

    IF (TG_OP = 'INSERT') THEN
        INSERT INTO log
            VALUES(
                now(),
                'inserted data is name : '||NEW.name||' num : '||NEW.number
                );
        RETURN NEW;
     ELSEIF (TG_OP = 'update') THEN
    INSERT INTO log VALUES
        (
            now(),
            'updated record with old num :'||OLD.number||' with new num : '||NEW.number
        );
    RETURN OLD;
    END IF;

    RETURN null;
END;
$BODY$
  LANGUAGE plpgsql

creating trigger:

CREATE TRIGGER add_log_trigger
AFTER INSERT OR UPDATE
ON nums
FOR EACH ROW
EXECUTE PROCEDURE add_log();

When an insert operation is performed, insert trigger is being fired.But when update operation is performed no update trigger is being fired.Why?

Upvotes: 3

Views: 851

Answers (1)

Kristján
Kristján

Reputation: 18843

String comparison is case-sensitive in Postgres. Try TG_OP = 'UPDATE' instead of TG_OP = 'update'.

Upvotes: 2

Related Questions