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

    Unanswered: Delete trigger...how do I know what columns the user has specified?

    Hi,

    I have a trigger that fires after a delete and re-sets a field value. This works, however I want to know what columns the user has specified in the delete command.

    I have put some comments in the delete action of the trigger, along with all the code...any ideas how to do this?

    Code:
    CREATE TABLE amenity_mappings (
      code varchar(10) NOT NULL,
      bitmask integer,
      category varchar(15) NOT NULL CONSTRAINT category_check CHECK (category = 'SUPPLIER' OR category = 'OPTION' OR category = 'ACCOM' OR category = 'ROOM' OR category = 'EXP'),
      description varchar(100),
      priority integer,
      sub_codes text[],
      PRIMARY KEY (code, category)
    );
    CREATE OR REPLACE FUNCTION set_amenity_bitmask() RETURNS trigger AS $$
    DECLARE
       row_number int4;
       bit_mask int4;
    
       -- row returned for delete
       row_data amenity_mappings%ROWTYPE;
    BEGIN
    
       IF TG_OP = 'INSERT' THEN
           SELECT 2^COUNT(*) INTO bit_mask FROM amenity_mappings WHERE category = NEW.category;
           NEW.bitmask = bit_mask;
           RETURN NEW;
       END IF;
    
       IF TG_OP  = 'DELETE' THEN
           row_number := 0;
           // how do I know what category and code has been specified in the delete command ????
    
           FOR row_data IN SELECT * FROM amenity_mappings WHERE category = OLD.category
           LOOP
               bit_mask := 2^row_number;
               UPDATE amenity_mappings SET bitmask = bit_mask WHERE code = row_data.code;
               row_number := row_number + 1;
           END LOOP;
           RETURN NULL;
        END IF;
     
    END;
    $$ LANGUAGE 'plpgsql';    
    
    CREATE TRIGGER update_bitmask_value BEFORE INSERT ON amenity_mappings FOR EACH ROW EXECUTE PROCEDURE set_amenity_bitmask();
    CREATE TRIGGER reindex_bitmask_value AFTER DELETE ON amenity_mappings FOR EACH ROW EXECUTE PROCEDURE set_amenity_bitmask();
    
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('POOL', 'SUPPLIER', 'Pool', 1, '{POOL,CHPL,HPLW,HPOL,LAGOON,LAPPL}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('CASP', 'SUPPLIER', 'Parking', 2, '{}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('BALC', 'SUPPLIER', 'Balcony', 3, '{}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('INTNET','SUPPLIER', 'Internet Access', 4, '{INNET, INET, INETB, INTB}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('BSVC','SUPPLIER','Business Services Available', 5, '{BSVC,SECR}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('REST','SUPPLIER','Onsite Restaurant/Cafe', 6, '{REST,RBYO,RLIC}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('ECO','SUPPLIER','Eco Accredited', 7, '{ECO,ECOA}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('TNIS','SUPPLIER','Tennis Court', 8, '{HTNS,TNIS}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('SPAB','OPTION','Spa in room', 1, '{SPAB,HTUB}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('WASH','OPTION','Washing Machine', 2, '{WASH,LNDYRM,WDRY}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('HOTEL', 'ACCOM', 'Hotel', 1, '{HOTEL}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('RESORT', 'ACCOM', 'Resort', 2, '{RESORT}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('MOTEL', 'ACCOM', 'Motel / Motor Inn', 3, '{MOTEL}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('APART', 'ACCOM', 'Apartments', 4, '{APART}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('LODGE', 'ACCOM', 'Lodge', 5, '{LODGE}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('BEDBRK', 'ACCOM', 'Bed & Breakfast', 6, '{BEDBRK}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('BOUT', 'ACCOM', 'Boutique', 7, '{BOUT}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('COTT', 'ACCOM', 'Cottages/Cabins/Houses', 8, '{COTT}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('HOSTEL', 'ACCOM', 'Hostel/Backpacker', 9, '{HOSTEL}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('CARA', 'ACCOM', 'Caravan/Tourist Park', 10, '{CARA}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('HOME', 'ACCOM', 'Homestay / Farmstay', 11, '{HOME}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('HOTEL', 'ROOM', 'Hotel Room', 1, '{HOTEL}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('SUITE', 'ROOM', 'Suite', 2, '{SUITE}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('STUDIO', 'ROOM', 'Studio', 3, '{STUDIO}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('1BDRM', 'ROOM', '1 Bedroom', 4, '{1BDRM}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('2BDRM', 'ROOM', '2 Bedroom', 5, '{2BDRM}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('3BDRM', 'ROOM', '3+ Bedroom', 6, '{3BDRM}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('DORM', 'ROOM', 'Dormitory', 7, '{DORM}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('BEACH', 'EXP', 'Beach / Island', 1, '{BEACH}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('CITY', 'EXP', 'City Escape', 2, '{CITY}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('ROMANCE', 'EXP', 'Romance', 3, '{ROMANCE}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('LUXUR', 'EXP', 'Luxury', 4, '{LUXUR}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('SPA', 'EXP', 'Spa', 5, '{SPA}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('ADVEN', 'EXP', 'Adventure', 6, '{ADVEN}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('ECO', 'EXP', 'Eco', 7, '{ECO}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('WINTER', 'EXP', 'Winter', 8, '{WINTER}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('FAMILY', 'EXP', 'Family', 9, '{FAMILY}');
    insert into amenity_mappings (code, category, description, priority, sub_codes) VALUES ('PETF', 'EXP', 'Pet-friendly', 10, '{PETF}');

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by AP81 View Post
    however I want to know what columns the user has specified in the delete command.
    I don't understand that question.
    A DELETE completely deletes an entire row. You don't specify columns when using DELETE

    Take this DELETE statement:
    Code:
    DELETE FROM the_table
      WHERE primary_key_column = 42;
    You never specify any column in there

    Or are your talking about the columns in the WHERE clause?
    I don't think there is a way to get hold of the values there.

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    Yes, I'm talking about the columns in the where clause.

    Ideally I want to force the user to specify:

    Code:
     where category =
    In other words, I want to make category mandatory. I also want to check if the code column is present in the where clause.

    I don't think it can actually be done...

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by AP81 View Post
    Yes, I'm talking about the columns in the where clause.

    Ideally I want to force the user to specify:

    Code:
     where category =
    In other words, I want to make category mandatory. I also want to check if the code column is present in the where clause.

    I don't think it can actually be done...
    You are right, you cannot enforce that.

    The only way you might be able to do something like that, is to revoke the DELETE privilege from the user running the statement and then supply a stored function that does the delete and ensures that a criteria is supplied.

    In order for that to work, the user accessing the database must not be the owner of the tables.

Posting Permissions

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