1

Using SQLFiddle, PostgreSQL 9.3.1.

I am learning to define triggers in PostgreSQL, and after doing some research I've found out the following:

Triggers in Postgres are different from MYSQL. Where in Postgres you must create a function that RETURNS TRIGGER, in MySQL you can just create a trigger. So this is what I've come up with:

On Employee Insert, we want to update Departments Total Salary.

CREATE FUNCTION update_sal() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.dno IS NOT NULL THEN
    UPDATE Department SET Total_sal = total_sal  + NEW.salary
    WHERE department.dno = NEW.dno;
  END IF;
RETURN NULL;
END;
$$ Language plpgsql;

CREATE TRIGGER updateInsert
AFTER INSERT ON Employee
FOR EACH ROW
EXECUTE PROCEDURE update_sal();

And I'm getting the following error:

Schema Creation Failed: ERROR: unterminated dollar-quoted string at or near "$$ 
BEGIN IF NEW.dno IS NOT NULL THEN UPDATE Department 
SET Total_sal = total_sal +NEW.salary WHERE department.dno = NEW.dno":
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Talen Kylon
  • 1,908
  • 7
  • 32
  • 60

2 Answers2

1

I've solved the issue thanks to Database Function giving an error - Postgresql

It seems just changing the query terminator at the bottom of the Scheme Window solves this issue.

Community
  • 1
  • 1
Talen Kylon
  • 1,908
  • 7
  • 32
  • 60
  • [Related answer with more details and demo](http://stackoverflow.com/questions/22747225/exceptions-when-creating-a-trigger-in-postgresql-9-1/22748778#22748778). – Erwin Brandstetter Apr 19 '14 at 12:59
0

If you copy-pasted the code, then you've got a simple syntax error: ENDl should be END; in the last-but-one line of the function definition.

Otherwise, it looks good to me.

scivi
  • 106
  • 5