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,
type char(1) NOT NULL,
PRIMARY KEY (code)
Here is my trigger so far:
CREATE TRIGGER set_amenity_bitmask AFTER INSERT OR UPDATE ON amenity_mappings
SELECT COUNT(*) INTO row_count FROM amenity_mappings;
row_count := row_count + 1;
bitmask = 2 ^ row_count;
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:
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.
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