Thread: How to notice column changes in trigger
How can I detect whether a column was changed by an update command inside a trigger? create table test(a int, b int, c int, primary key(a)) b and c should be updated inside an update trigger if not modified by the statement itself 1) update test set a=0 -> trigger does its work 2) update test set a=0, b=1, c=2 -> trigger does nothing 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of a and b dont change either although touched What I'm looking for is something like IF NOT COLUMN_TOUCHED(b) THEN ... For MSSQL, this would be coded as IF NOT UPDATE(b) .. IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger code. Any hints? Andreas
> > How can I detect whether a column was changed by an update command > inside a trigger? > > create table test(a int, b int, c int, primary key(a)) > > b and c should be updated inside an update trigger if not modified by > the statement itself > > 1) update test set a=0 -> trigger does its work > 2) update test set a=0, b=1, c=2 -> trigger does nothing > 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of > a and b dont change either although touched > > What I'm looking for is something like > IF NOT COLUMN_TOUCHED(b) THEN ... > For MSSQL, this would be coded as IF NOT UPDATE(b) .. > > IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger code. > Sorry, but I don't get it. Does > b and c should be updated inside an update trigger if not modified by > the statement itself mean b=b not modified, b=1 modified even if b was 1 before ? So you want the trigger executed only if assignments to b and/or c do not appear within the update command. Right? Regards, Christoph
Christoph Haller wrote: >So you want the trigger executed only if assignments to b and/or c do >not appear within the update command. Right? > >Regards, Christoph > > Right, that's what I want.
Andreas, > 1) update test set a=0 -> trigger does its work > 2) update test set a=0, b=1, c=2 -> trigger does nothing > 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of > a and b dont change either although touched > IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger > code. I still don't get why you'd want to do this. Can you provide are real-world example where there is a difference between setting B=B and not updating B? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: >Andreas, > > > >>1) update test set a=0 -> trigger does its work >>2) update test set a=0, b=1, c=2 -> trigger does nothing >>3) update test set a=0, b=b, c=c -> trigger does nothing, but content of >>a and b dont change either although touched >> >> > > > >>IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger >>code. >> >> > >I still don't get why you'd want to do this. Can you provide are real-world >example where there is a difference between setting B=B and not updating B? > > > I do. It's about tracking changing user and timestamp. Normally, all inserts and updates won't touch the b and c columns, and they are handled using triggers and/or default values.But sometimes, I'd like to update some columns (other than b or c) WITHOUT having changed b or c. This could happen for some import tasks, for example. In this case, the user/timestamp from the exporting database should be replicated, not altered. In other cases, some maintenance procedure should calculate a column different from b and c, which would lead to timestamp set to lets say midnight, and the user to "serviceProcess" which would destroy the original data. In this case, I'd set b and c to the original data. That's the way I can handle this with MSSQL.
> > >So you want the trigger executed only if assignments to b and/or c do > >not appear within the update command. Right? > > > > Right, that's what I want. > I'm afraid I have no idea how to accomplish that. Regards, Christoph
Hello Christoph Haller , > > > > >So you want the trigger executed only if assignments to b and/or c do Do sime thing like this use if /then /esle s block for the problem In the first if blick check that b and c is null by the declaration of IS NULL after that you run the update command as required The program code will show as create function CREATE FUNCTION BLAH_FUNCTION() RETURNS "trigger" AS ' usual blah blah of declre and all , IF NEW.b IS NULL and NEW.C IS NULL THEN ROCK BABY ELSE Do OTHERWISE END IF; more blah blah plpgsql'; CREATE TRIGGER BLAH BEFORE UPDATE ON BLAH_TABLE FOR EACH ROW EXECUTE PROCEDURE BLAH_FUNCTION Please revert back if this helps . Regards V Kashyap > > >not appear within the update command. Right? > > > > > > > Right, that's what I want. > > > I'm afraid I have no idea how to accomplish that. > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Aspire Something wrote: > Hello Christoph Haller , > > > >>>>So you want the trigger executed only if assignments to b and/or c do > > > Do sime thing like this use if /then /esle s block for the problem > > In the first if blick check that b and c is null by the declaration of > IS NULL after that you run the update command as required > > The program code will show as > > create function > CREATE FUNCTION BLAH_FUNCTION() RETURNS "trigger" > AS ' > usual blah blah of declre and all , > > > IF NEW.b IS NULL and NEW.C IS NULL THEN > ROCK BABY > ELSE > Do OTHERWISE > END IF; > more blah blah plpgsql'; > > Sorry, this won't work at all. The NEW will contain the new row contents, and that may well be non-null for columns not mentioned in the update query. This kind of code will perform well on inserts, but not on updates of well-populated rows. Core SQL or PL/PGSQL statements won't do the job. Regards, Andreas
Josh Berkus wrote: > > Andreas, > > > 1) update test set a=0 -> trigger does its work > > 2) update test set a=0, b=1, c=2 -> trigger does nothing > > 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of > > a and b dont change either although touched > > > IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger > > code. > > I still don't get why you'd want to do this. Can you provide are real-world > example where there is a difference between setting B=B and not updating B? Setting b=b and not updating it is indeed identical ... at least after the targetlist completion in PostgreSQL. But it is different from b=<b's-old-value>, and the fact that we cannot distinguish between these two (inside the trigger) prevents us from skipping foreign key checks if your fk-values haven't been touched. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #