[GENERAL] question on trigger
text/html; charset="utf-8": Unrecognized inline: top.letterhead
Re: [GENERAL] question on trigger
On Saturday, February 11, 2012 09:22:44 AM mgo...@isstrucksoftware.net wrote: I will have several triggers which will make sure that the data in some columns in in uppercase format. For insert it is a no brainer, however for updates, it is better to check and see if the new values is the same and only update if different? We are not talking about a huge number of transactions. In one day we might have at the most 3000 transactions. Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Michael: I am by no means an expert on database performance; however, it seems to me that you would waste more cycles checking if things changed than simply forcing upper case for all to which this criteria applies. The whole record is going to be written to the database anyway. Terry Lee Tucker Office: 336-372-6812
[GENERAL] Question on trigger data visibility
Hi, Assume tablex, tabley and tablez are correctly populated in my database. My purpose is to enforce referential integrity between a column in the tablex (the child) and a column in tablez (the parent). Since normal foreign keys do not give me this functionality, I decide to write a trigger. My trigger function looks something like: CREATE OR REPLACE FUNCTION trigger_on_tablex() RETURNS trigger AS $$ BEGIN PERFORM 1 FROM tablex AS tab_x INNER JOIN tabley AS tab_y ON tab_x.gp = tab_y.id INNER JOIN tablez AS tab_z ON tab_y.ml = tab_z.id WHERE tab_x.name = tab_z.name; IF NOT FOUND THEN RAISE EXCEPTION 'constraint violated '; END IF; END;$$ LANGUAGE plpgsql; CREATE TRIGGER mytrigger AFTER INSERT ON tablex FOR EACH STATEMENT EXECUTE PROCEDURE trigger_on_tablex(); My problem is that no matter what I insert into tablex, the exception is always raised. So, it seems that even though my trigger is defined as AFTER INSERT FOR EACH STATEMENT, the inserted row does not appear to be included in the join. So, now to my question: Should, as a matter of principle, statement level triggers not see rows recently inserted into the tablex? Thanks, Maurice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on trigger data visibility
On Monday, August 30, 2010 07:20:14 Maurice Gittens wrote: Hi, Assume tablex, tabley and tablez are correctly populated in my database. My purpose is to enforce referential integrity between a column in the tablex (the child) and a column in tablez (the parent). Since normal foreign keys do not give me this functionality, I decide to write a trigger. My trigger function looks something like: CREATE OR REPLACE FUNCTION trigger_on_tablex() RETURNS trigger AS $$ BEGIN PERFORM 1 FROM tablex AS tab_x INNER JOIN tabley AS tab_y ON tab_x.gp = tab_y.id INNER JOIN tablez AS tab_z ON tab_y.ml = tab_z.id WHERE tab_x.name = tab_z.name; IF NOT FOUND THEN RAISE EXCEPTION 'constraint violated '; END IF; END;$$ LANGUAGE plpgsql; CREATE TRIGGER mytrigger AFTER INSERT ON tablex FOR EACH STATEMENT EXECUTE PROCEDURE trigger_on_tablex(); My problem is that no matter what I insert into tablex, the exception is always raised. So, it seems that even though my trigger is defined as AFTER INSERT FOR EACH STATEMENT, the inserted row does not appear to be included in the join. So, now to my question: Should, as a matter of principle, statement level triggers not see rows recently inserted into the tablex? Thanks, Maurice They do see those rows. Are you sure that the inner join with tab_Y is not causing the problem? Just a guess... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on trigger data visibility
Terry Lee Tucker te...@chosen-ones.org writes: On Monday, August 30, 2010 07:20:14 Maurice Gittens wrote: So, it seems that even though my trigger is defined as AFTER INSERT FOR EACH STATEMENT, the inserted row does not appear to be included in the join. So, now to my question: Should, as a matter of principle, statement level triggers not see rows recently inserted into the tablex? They do see those rows. Are you sure that the inner join with tab_Y is not causing the problem? Just a guess... It also seems worth pointing out that this trigger would hardly ensure referential integrity. As quoted, it would succeed so long as there is at least one tablex row that is properly referencing some tablez row. Surely you want to require that they *all* do. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
