Triggers: how to use id with FOR EACH ROW

Posted on 2009-04-23
Last Modified: 2012-05-06
Take the trigger below. In PostgreSQL 8.3, how can I use the id of the row that the procedure is executed on in the function test_update()?
CREATE TRIGGER test_update_trigger


  ON testtable


  EXECUTE PROCEDURE test_update();

Open in new window

Question by:R7AF
    LVL 12

    Expert Comment

    If you want to access the 'id' column from the row, you can use either or inside the function test_update().  (If the update has not changed the value of the 'id' column, these will be the same; if the update has changed this column, then it is up to you to decide which value you want to use.)  See docs on Trigger Procedures here:

    If you want the row id relative to the set of rows which have set off the trigger (that is, 5 records in a table of 1000 are updated, and you want 1, 2, 3, 4, or 5 available to each execution of test_update), my initial response is that it is not possible, or at least PostgreSQL does not make that information available to the trigger function natively (unless they haven't documented the feature).  And every thought for a work-around solution will likely not hold up in practice (mainly fail with concurrent triggers).

    So I hope the first suggestion is what you were looking for.
    LVL 22

    Accepted Solution

    note BEFORE UPDATE and use of
      new.updated := now()
      return new;

    ~]$ psql treacle;
    Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands    
           \? for help with psql commands  
           \g or terminate with semicolon to execute query
           \q to quit                                    

    treacle=> CREATE TABLE testtable
    treacle-> (                    
    treacle(>   id serial NOT NULL,
    treacle(>   label text,        
    treacle(>   created timestamp with time zone DEFAULT now(),
    treacle(>   updated timestamp with time zone DEFAULT now(),
    treacle(>   deleted boolean,                              
    treacle(>   description text                              
    treacle(> )                                                
    treacle-> WITH (OIDS=FALSE);                              
    NOTICE:  CREATE TABLE will create implicit sequence "testtable_id_seq" for serial column ""
    treacle=> CREATE FUNCTION test_update() RETURNS trigger AS $$
    treacle$> begin                                                                                        
    treacle$>   new.updated := now();                                                                      
    treacle$>   return new;                                                                                
    treacle$> end                                                                                          
    treacle$> $$ LANGUAGE plpgsql;
    CREATE FUNCTION                                                                                        
    treacle=> CREATE TRIGGER test_update_trigger
    treacle-> BEFORE UPDATE                                                                                
    treacle-> ON testtable                                                                                
    treacle-> FOR EACH ROW                                                                                
    treacle-> EXECUTE PROCEDURE test_update();  
    CREATE TRIGGER                                                                                        
    treacle=> insert into testtable values ( default, 'First', default, default, false, ' Record' );  
    INSERT 0 1                                                                                        
    treacle=> select * from testtable;
     id | label |           created           |           updated           | deleted | description
      1 | First | 2009-04-24 07:04:06.0572+01 | 2009-04-24 07:04:06.0572+01 | f       |  Record    
    (1 row)                                                                                        
    treacle=> update testtable set description = ' Hello World second time!';
    UPDATE 1
    treacle=> select * from testtable;
     id | label |           created           |            updated            | deleted |        description
      1 | First | 2009-04-24 07:04:06.0572+01 | 2009-04-24 07:07:08.139084+01 | f       |  Hello World second time!
    (1 row)
    LVL 13

    Author Closing Comment

    I thought I needed two questions for this problem, but well...
    For reference:

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now