Trigger updating column


We have a simple database of usernames and (encrypted) passwords with the following definition: Rather than tackling each of these independently, using a trigger we can address them all at the same time, and all from within the database.

We create our trigger function using PL/pg SQL - which comes built in in Postgre SQL 9.0 and later, and can be installed as an add-on for earlier versions.


In the following example we want a database table (users) to keep track of when one of it's fields (password) was last updated.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

1) Row level trigger - An event is triggered for each row upated, inserted or deleted.

# \d users Table "public.users" Column | Type | Modifiers ------------------+-------------------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | character varying | not null password | character varying | not null password_changed | date | default now() Indexes: "users_username_key" UNIQUE CONSTRAINT, btree (username) Triggers: trigger_password_changed BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE List of functions -[ RECORD 1 ]-------+----------------------------------------- Schema | public Name | password_changed Result data type | trigger Argument data types | Type | trigger Security | invoker Volatility | volatile Owner | postgres Language | plpgsql Source code | | BEGIN | IF NEW.password !

= OLD.password THEN | NEW.password_changed := current_date; | END IF; | RETURN NEW; | END; Because the trigger is defined within the database it will survive any normal backup and restore process unaffected.


  1. Pingback:

  2. eric   •  

    Cybercriminals constantly look for and find opportunities to compromise retail environments.

  3. eric   •  

    You’ve heard the clichés, and by now you’re sick of them. But what if you just happen to be someone who wants to stay in Vegas—a permanent resident?

  4. eric   •  

    Roof was captured Thursday morning in Shelby, Buy Viagra North Carolina, Generic Viagra online according to Attorney General Loretta Lynch.

  5. eric   •  

    We are powered by the latest in video and audio software. Whether you prefer busier or quieter rooms, regardless of race, age or sex, we have you covered. Connect online with thousands of other users today.

  6. eric   •  

    You are now able to pay by Echeck to any adult webcam sex site and do so with great ease and efficiency.

  7. eric   •  

    There are thousands of active singles on Date looking to chat right now.

  8. eric   •  

    It included the #1 rap hit singles "Bounce With Me" featuring the R&B girl group Xscape, and "Bow Wow (That's My Name)." The album hit the top 10 on the album chart and ultimately sold more than two million copies.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>