Bookmarks
Account
ACL
Tools
New
Other
NewTrigger - (Easy is a Relative Term)
If you are proficient with PGSQL, let us know via the Contact form. Novices will be able to Find Experts - & Collaborate on Projects.
for more, go to the PostgreSQL manual
New Trigger
1
2
SynopsisCREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )
DescriptionCREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table and will execute the specified function funcname when certain events occur.
The trigger can be specified to fire either before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted) or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed). If the trigger fires before the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the last insertion, update, or deletion, are "visible" to the trigger.
A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR EACH STATEMENT triggers).
If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
DescriptionCREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table and will execute the specified function funcname when certain events occur.
The trigger can be specified to fire either before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted) or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed). If the trigger fires before the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the last insertion, update, or deletion, are "visible" to the trigger.
A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR EACH STATEMENT triggers).
If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
After you create your Table, you will need to create a special type of Function (trigger function). In this example, we want to make sure that if someone is doing data entry - and leaves employee name blank - or salary blank - or a negative salary, that they will be stopped - and - given an explanation (Raise Exception).
The example below assumes that you have previously created a Table that looks like this:
CREATE
Click Create and assuming you have not made an error, you are done with Step 1. Next, you would navigate (using the YTree) to your Table.
You want a Trigger on this Table
Click Triggers
Click Add Row twice (2x). You want the Trigger to fire the Function 'emp_stamp' BEFORE an Insert or Update to your Table.
When you are done, click Save.
Now Test your work by trying to enter some data -- leave 'salary' empty (NULL), then click 'Commit'.
Click here to see how to data enter values into a Table.
Commit
You will get:
ERROR: Joe Blow really DOES need to get paid something
ERROR: Joe Blow really DOES need to get paid something
Note that you could have had the same 'effect' by applying Constraints. The method above gives an error message that may be more helpful.