[SQL] NEW and OLD as ROWS
I am writing an update trigger that I want to fire when any field changes. It looks like two ROWS can be compared very easily, but not the two RECORDS OLD and NEW. Is there a way to do: NEW IS DISTINCT FROM OLD in a trigger? This would help me not have to write a specific trigger for each table. Thanks, Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Updateing pg_trigger and pg_constraint
I am trying to make some foreign keys deferrable and initially deferred. These foreign key constraints already exist so I was going to change them by updating pg_trigger and pg_constraint. However the changes do not seem to take affect. Is there something I need to do to get PostgreSQL to recognize that I have tweaked it's tables? This is the query that I used: begin work; update pg_constraint set condeferrable = true, condeferred = true where contype ='f'; update pg_trigger set tgdeferrable = true, tginitdeferred = true where tgconstrname in ( select conname from pg_constraint where contype = 'f' ); commit any help would be appreciated, Craig ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trigger efficiency
I do not know of a way to make your trigger run less often, but you could only have it do the insert if something changes. This is what we do: CREATE or replace FUNCTION UPDATE_SERVER_HST() RETURNS TRIGGER AS ' begin if (OLD.ADD_DATE is distinct from NEW.ADD_DATE or OLD.HOSTNAME is distinct from NEW.HOSTNAME ) then insert into SERVER_HST( OPERATION, HST_USR_ID, HST_ADD_DATE, SERVER_ID, UPDATE_COUNT, ADD_DATE, HOSTNAME) values( substr( TG_OP, 1, 1 ), GET_CONNECTION_USR(), now() , OLD.SERVER_ID, OLD.UPDATE_COUNT, OLD.ADD_DATE, OLD.HOSTNAME); end if; RETURN NULL; -- result is ignored since this is an AFTER trigger end; ' language plpgsql security definer; That way the insert only happens if there is a change. Since this is a pain we have a program that writes our history and update_count triggers for us based on what we run through our modeling tool. Craig On Monday 06 February 2006 06:48, Josep Sanmartí wrote: > Hi, > > I've the following problem and I don't know how to solve it: There is a > table with about 10-12 fields, a couple of those fields are updated very > often (about 30 times / minute or even more). This is my table (more or > less): > > crete table monitor( > time Timestamp, > time2 timestamp, > ... > status int, > ); > > I need to know how many rows are in the table and keep that number to > another table every time that there is an INSERT or DELETE on that > table, so I made a trigger that fires on insert and delete events and > calls a function that makes de update on the new table, that works well. > My problem starts when I have to count the rows depending on the status > field: I added a UPDATE on my trigger and it works! :).. my trigger: > > CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE > ON monitor EXECUTE PROCEDURE f_barra_aps(); > > But now, this trigger fires too often and there is an extra load on the > system that makes everything run slower. I posted before a question > asking about firing triggers on specific columns...thats not possible. I > don't know how to solve it, perhaps I should do it without > triggers.any help will be appreciated > > thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Statement Triggers and access to other rows in the transaction
I have tables representing a ledger system and the accounts on which it operates. I want to do 2 things to all transactions altering the ledger tables. First, all of the inserts into the ledger tables should be balanced ( They should sum to 0 ). If not I want to abort the transaction. Second, if the transaction is balanced I want to update the account balance on the account tables. In other database systems that I have used when you have a statement level trigger you get access to the inserted/updated/deleted rows from that transaction as "virtual" tables within the trigger that you can manipulate. I am currently using plpgsql to write my triggers. I have not been able to figure out the "right" way to do this in PostgreSQL and would appreciate any advice. Thanks, Craig ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
