|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 19th, 2003, 01:38 PM
|
|||
|
|||
|
Trigger example needed.
Just starting out with PostgreSQL, and as I learn best by dissecting examples, I'm hoping for someone to give me the answer to the following:
Upon insertion of a new row a table entry, I'd like to insert an additional row in table thread. The tables are laid out as follows (only relevant columns shown): Code:
entry ( entry_id int4, entry_title varchar(255) ) thread ( thread_entry_id int4, thread_title varchar(255) ) After the insert on table entry, I want the following insert on table thread: Code:
INSERT INTO thread (thread_entry_id, thread_title) VALUES (NEW.entry_id, NEW.entry_title) If someone can give me the complete statements for the function and the trigger I'm sure I'll be able to figure out how to do the rest of the triggers. Btw, I've been succesful in creating functions, but this baffles me. Thanks. -- Charles.
|
|
#2
December 19th, 2003, 03:27 PM
|
|||
|
|||
|
you can probably use AFTER INSERT in the trigger creation, but if you use BEFORE it will just put that record in thread just how you want. you're not doing any iteration or any dependent INSERTion into thread, so you don't need any control structures or cursors (by not including these ideas in this example you are missing out on some of the key concepts of plpgsql). If you are repeatedly dropping and recreating the triggers on your db then you will have to do a dropdb and createdb for each time, because if you drop a trigger and then create a new one with the same name then run it, the function will be looking for the wrong OID.
CREATE OR REPLACE FUNCTION fill_thread_too RETURNS OPAQUE AS ' DECLARE BEGIN INSERT INTO thread (thread_entry_id, thread_title) VALUES (NEW.entry_id, NEW.entry_title); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER fill_thread_trigger BEFORE INSERT ON entry FOR EACH ROW EXECUTE PROCEDURE fill_thread_too();
|
|
#3
December 19th, 2003, 03:37 PM
|
|||
|
|||
|
don't think I am trying to be an *** or something, but I am going to post a slightly more involved trigger procedure that may assist you in the future. I have posted these functions before so it's sort of redundant for others who may read this so I am sorry I don't want to write any new ones ri now (it's friday)
Although it pretty clearly represents our schema here at work I think it is an abstract enough notion (and really a small piece of the db) that it is no matter to them (and it's code that I wrote) Code:
CREATE OR REPLACE FUNCTION mbc_update_function () RETURNS OPAQUE AS '
DECLARE
old_key_refcursor refcursor;
new_key_refcursor refcursor;
old_count_refcursor refcursor;
new_count_refcursor refcursor;
get_current_key CURSOR FOR SELECT MAX(combination_key) FROM mediacombinations;
old_key INT4;
new_key INT4;
old_new_count INT4;
new_new_count INT4;
new_combination_key INT4;
BEGIN
OPEN old_key_refcursor FOR SELECT combination_key FROM mediacombinations WHERE media_type = OLD.media_type AND media_size = OLD.media_size AND media_orientation = OLD.media_orientation AND sheets_per_media_cassette = OLD.sheets_per_media_cassette AND target_printer = OLD.target_printer AND allocated = OLD.allocated;
FETCH old_key_refcursor INTO old_key;
CLOSE old_key_refcursor;
IF (old_key IS NOT NULL) THEN
OPEN old_count_refcursor FOR SELECT total_count FROM mediacombinations WHERE combination_key = old_key;
FETCH old_count_refcursor INTO old_new_count;
old_new_count:=old_new_count-1;
-- UPDATE mediacombinations SET total_count = old_new_count WHERE media_type = OLD.media_type AND media_size = OLD.media_size AND media_orientation = OLD.media_orientation AND sheets_per_media_cassette = OLD.sheets_per_media_cassette AND target_printer = OLD.target_printer AND allocated = OLD.allocated;
UPDATE mediacombinations SET total_count = old_new_count WHERE combination_key = old_key;
CLOSE old_count_refcursor;
OPEN new_key_refcursor FOR SELECT combination_key FROM mediacombinations WHERE media_type = NEW.media_type AND media_size = NEW.media_size AND media_orientation = NEW.media_orientation AND sheets_per_media_cassette = NEW.sheets_per_media_cassette AND target_printer = NEW.target_printer AND allocated = NEW.allocated;
FETCH new_key_refcursor INTO new_key;
CLOSE new_key_refcursor;
IF (new_key IS NOT NULL) THEN
OPEN new_count_refcursor FOR SELECT total_count FROM mediacombinations WHERE combination_key = new_key;
FETCH new_count_refcursor INTO new_new_count;
new_new_count:=new_new_count+1;
UPDATE mediacombinations SET total_count = new_new_count WHERE media_type = NEW.media_type AND media_size = NEW.media_size AND media_orientation = NEW.media_orientation AND sheets_per_media_cassette = NEW.sheets_per_media_cassette AND target_printer = NEW.target_printer AND allocated = NEW.allocated;
CLOSE new_count_refcursor;
ELSE --new_key was null and that combination not added yet
OPEN get_current_key;
FETCH get_current_key INTO new_combination_key;
CLOSE get_current_key;
new_combination_key:=new_combination_key+1;
INSERT INTO mediacombinations ( combination_key, media_size, media_type, media_orientation, sheets_per_media_cassette, target_printer, total_count, legal_flag, allocated) VALUES ( new_combination_key, NEW.media_size, NEW.media_type, NEW.media_orientation, NEW.sheets_per_media_cassette, NEW.target_printer, 1, ''f'', NEW.allocated);
END IF;
ELSE
OPEN old_count_refcursor FOR SELECT total_count FROM mediacombinations WHERE combination_key = old_key;
FETCH old_count_refcursor INTO old_new_count;
old_new_count:=old_new_count-1;
UPDATE mediacombinations SET total_count = old_new_count WHERE media_type = OLD.media_type AND media_size = OLD.media_size AND media_orientation = OLD.media_orientation AND sheets_per_media_cassette = OLD.sheets_per_media_cassette AND target_printer = OLD.target_printer AND allocated = OLD.allocated;
CLOSE old_count_refcursor;
OPEN get_current_key;
FETCH get_current_key INTO new_combination_key;
CLOSE get_current_key;
new_combination_key:=new_combination_key+1;
INSERT INTO mediacombinations ( combination_key, media_size, media_type, media_orientation, sheets_per_media_cassette, target_printer, total_count, legal_flag, allocated) VALUES ( new_combination_key, NEW.media_size, NEW.media_type, NEW.media_orientation, NEW.sheets_per_media_cassette, NEW.target_printer, 1, ''f'', NEW.allocated);
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql'; --return to single quote
DROP TRIGGER mbc_update_trigger ON mediabarcodes;
CREATE TRIGGER mbc_update_trigger
BEFORE UPDATE
ON mediabarcodes
FOR EACH ROW
EXECUTE PROCEDURE mbc_update_function();
|
|
#4
December 19th, 2003, 04:59 PM
|
||||
|
||||
|
Quote:
Huh? So if I drop a trigger and wish to recreate it using the same name I need to dump/reload the whole db? Quote:
I assume I can safely use RETURNS TRIGGER instead of RETURNS OPAQUE? I'm using 7.4 and that's how I interpret this page. Anyway, thanks a million. The code I had tried was really different (translated from Oracle).
|
|
#5
December 19th, 2003, 05:17 PM
|
|||
|
|||
|
The statements were accepted, but it fails on execution :-(. The error message is:
ERROR: syntax error at or near "NEW" at character 261 Here's the code: Code:
CREATE OR REPLACE FUNCTION dvnl.create_matching_thread() RETURNS TRIGGER AS ' DECLARE BEGIN INSERT INTO dvnl.dvnl_thread (thread_id, thread_is_active, thread_user_id, thread_total_messages, thread_entry_id, thread_forum_id, thread_last_modified, thread_date_opened, thread_label) VALUES (DEFAULT, true, 100000, 0, NEW.entry_id, 100000, DEFAULT, DEFAULT, NEW.entry_title) RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER create_matching_thread_trigger BEFORE INSERT ON dvnl.mt_entry FOR EACH ROW EXECUTE PROCEDURE dvnl.create_matching_thread(); Here's the definition of the threads table: Code:
CREATE TABLE dvnl.dvnl_thread ( thread_id serial NOT NULL, thread_is_active bool NOT NULL DEFAULT false, thread_user_id int4 NOT NULL, thread_total_messages int4 NOT NULL DEFAULT 0, thread_entry_id int4, thread_forum_id int4 NOT NULL, thread_last_modified timestamp(0) NOT NULL DEFAULT now(), thread_date_opened timestamp(0) NOT NULL DEFAULT now(), thread_date_closed timestamp(0), thread_label varchar(255) NOT NULL, CONSTRAINT pk_thread_id PRIMARY KEY (thread_id), CONSTRAINT fk_forum_id FOREIGN KEY (thread_forum_id) REFERENCES dvnl.dvnl_forum (forum_id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_user_id FOREIGN KEY (thread_user_id) REFERENCES dvnl.dvnl_user (user_id) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT not_empty_thread_label CHECK thread_label::text <> ''::text ) WITH OIDS; I checked the manual, and I believe the NEW syntax is correct. I should tell you that all tables reside in schema 'dvnl', but I guess for an expert like you that's clear from the code. -- Charles.
|
|
#6
December 19th, 2003, 05:55 PM
|
|||
|
|||
|
Solved!
Of course, it was a typo on my end:
Code:
CREATE OR REPLACE FUNCTION dvnl.create_matching_thread() RETURNS TRIGGER AS ' DECLARE BEGIN INSERT INTO dvnl.dvnl_thread (thread_id, thread_is_active, thread_user_id, thread_total_messages, thread_entry_id, thread_forum_id, thread_last_modified, thread_date_opened, thread_label) VALUES (DEFAULT, true, 100000, 0, NEW.entry_id, 100000, DEFAULT, DEFAULT, NEW.entry_title); <-- missing colon! RETURN NEW; END; ' LANGUAGE 'plpgsql'; -- Charles.
|
|
#7
December 19th, 2003, 06:00 PM
|
|||
|
|||
|
Now, when the entry is deleted, the thread and all the entries in the message table should also be deleted. What would be best:
1. cascaded delete 2. trigger -- Charles.
|
|
#8
December 19th, 2003, 06:37 PM
|
|||
|
|||
|
Quote:
1. It depends on how strongly you have defined the referential integrity of the tables. I believe using ON DELETE CASCADE would have to be using tables that can be NATURAL JOINed (i.e. foreign keys have the same column names) also, have you defined the tables with explicit CONSTRAINT syntax foreign keys, or how is it mentioned in the table creation? Either one will work just fine in fact I think there are 3 ways to make a foreign key in the table creation but I just wanted to see if that is what you had done. 2. Sure, you can write a trigger to do a delete. Remember to return the appropriate value RETURN OLD or RETURN NULL depending on if you want the DELETE to succeed or not.
|
|
#9
December 19th, 2003, 06:50 PM
|
|||
|
|||
|
Quote:
I have had trouble in the past, yes. If you delete the trigger and the function, then it should be ok. I do'nt know if it really does the CREATE OR REPLACE thing well though, anyways just make a copy with createdb --template=somedb and then test your triggers in there
|
|
| Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Trigger example needed. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
