Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

38.6. PL/Perl Triggers

PL/Perl can be used to write trigger functions. In a trigger function, the hash reference $_TD contains information about the current trigger event. The fields of the $_TD hash reference are:

$_TD->{new}{foo}

NEW value of column foo

$_TD->{old}{foo}

OLD value of column foo

$_TD->{name}

Name of the trigger being called

$_TD->{event}

Trigger event: INSERT, UPDATE, DELETE, or UNKNOWN

$_TD->{when}

When the trigger was called: BEFORE, AFTER, or UNKNOWN

$_TD->{level}

The trigger level: ROW, STATEMENT, or UNKNOWN

$_TD->{relid}

OID of the table on which the trigger fired

$_TD->{relname}

Name of the table on which the trigger fired

$_TD->{argc}

Number of arguments of the trigger function

@{$_TD->{args}}

Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.

Triggers can return one of the following:

return;

Execute the statement

"SKIP"

Don't execute the statement

"MODIFY"

Indicates that the NEW row was modified by the trigger function

Here is an example of a trigger function, illustrating some of the above:

CREATE TABLE test (
    i int,
    v varchar
);

CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
    if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
        return "SKIP";    # skip INSERT/UPDATE command
    } elsif ($_TD->{new}{v} ne "immortal") {
        $_TD->{new}{v} .= "(modified by trigger)";
        return "MODIFY";  # modify row and execute INSERT/UPDATE command
    } else {
        return;           # execute INSERT/UPDATE command
    }
$$ LANGUAGE plperl;

CREATE TRIGGER test_valid_id_trig
    BEFORE INSERT OR UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE valid_id();


 
 
  Published courtesy of The PostgreSQL Global Development Group Design by Interspire