CREATE OR REPLACE FUNCTION msgfailerror() RETURNS trigger AS
' BEGIN
IF NEW.noces< new.first_column THEN
RAISE EXCEPTION 'cannot have a negative salary';
END IF;
return new;
END' LANGUAGE plpgsql
Trigger
create trigger msgfail before insert on first for each row
execute procedure msgfailerror()
Giving error:
syntax error at or near "cannot" LINE 5: RAISE EXCEPTION 'cannot have a negative ...
I have almost one validation for each field of row. I want trigger to check all validations while insertion is being done and, raise error log afterwards once for all. Should I use raise exception on raise notice ?
For example:
Insert into first (first_column, noces,dob) values ('4545','75','545')
I am checking noces
is less than first_column
, for the same row i want to check if dob > 80 and if first_column is integer and raise error for all validations. Thanks in advance
The quoting is wrong. It's easier to use dollar quotes $$:
CREATE OR REPLACE FUNCTION msgfailerror()
RETURNS trigger AS
$$
BEGIN
IF NEW.noces< new.first_column THEN
RAISE EXCEPTION 'cannot have a negative salary';
END IF;
return new;
END;
$$
LANGUAGE plpgsql;
But on the other hand, what's wrong with a check constraint?