Database Reference
In-Depth Information
We will now create a
perl_trigger_func
function that checks the values of
emp_id
before an
INSERT
operation. It will skip the operation if it is greater than or equal to
10
. If it is smaller than
10
it will insert the row:
warehouse_db=# CREATE EXTENSION plperl;
CREATE OR REPLACE FUNCTION perl_trigger_func()
RETURNS trigger AS $$
IF ($_TD->{event} = 'INSERT') {
IF (($_TD->{new}{emp_id} >= 10)) {
RETURN "SKIP";}
ELSE {
RETURN;}
}
$$ LANGUAGE plperl;
Now like all triggers, associate this with the table,
tab_perl
in this case. This is done
in the following manner:
warehouse_db=# CREATE TRIGGER perl_trigger
BEFORE INSERT ON tab_perl
FOR EACH ROW
EXECUTE PROCEDURE perl_trigger_func();
Now, try to insert a row with
emp_id
greater than
10
and check content of the
table afterwards.
Insert the row in the
tab_perl
table using the following statement:
warehouse_db=# INSERT INTO tab_perl VALUES (13, 'Roger',
'Boston');
Check the content of the table using the following statement:
warehouse_db=# SELECT * FROM tab_perl;
emp_id | emp_name | emp_city
--------+----------+----------
1 | Adam | Chicago
2 | John | Miami
(2 rows)
So,
INSERT
is skipped. Now, let's add a row with
emp_id
smaller than
10
this time.
Insert the row in the
tab_perl
table using the following statement:
warehouse_db=# INSERT INTO tab_perl VALUES (3, 'Roger', 'Boston');
