Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010

    Unanswered: Insert Trigger - help needed


    I am trying to write a trigger which fires on insert, and updates a specific column.

    Bascially, when any data is inserted into the table, the column called 'bitmask' needs to be calculated (even if bitmask has been specified in the insert query).

    For example, if there are five rows in the table, the next insert needs to set bitmask as 2^6. The insert after that will be 2^7, etc.

    The best way I see to do this is to get the row count of the table, then add 1 to it. ie. value := row_count +1; bitmask := 2^value;

    I'm having a bit of trouble creating this trigger. I've started creating the code (see below) but am a bit lost. How do I know which row is the current row the trigger is operating on? Any advice is appreciated.

    Here is the table:

    CREATE TABLE amenity_mappings (
      code varchar(10) NOT NULL,
      bitmask integer,
      type char(1) NOT NULL,
      description varchar(100),
      priority integer,
      sub_codes text[],
      PRIMARY KEY (code)
    Here is my trigger so far:
    CREATE TRIGGER set_amenity_bitmask AFTER INSERT OR UPDATE ON amenity_mappings
       row_count int4;
       bitmask int4;
       SELECT COUNT(*) INTO row_count FROM amenity_mappings;
       row_count := row_count + 1;
       bitmask = 2 ^ row_count;

  2. #2
    Join Date
    May 2008
    You really should take a look at the documentation on triggers.

    First, you need to create a function for your trigger. Then you need to establish a trigger on the table that invokes the appropriate function. For row-level triggers, your trigger function has automatic access to the NEW and OLD variables. So the body of your function will look something like:

    SELECT (COUNT(*)+1)^2 
    INTO NEW.bitmask
    FROM amenity_mappings;
    You should also make this a BEFORE trigger, otherwise every row will basically be updated twice. I'll also point out that an UPDATE does not change the number of rows in a table, so depending on what you're trying to accomplish, you might only need a BEFORE INSERT trigger.

  3. #3
    Join Date
    Jun 2004
    Arizona, USA
    What happens if you need to delete a row?

    Also noted; the table can never have more than 32 rows...
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts