Home » RDBMS Server » Performance Tuning » Troublesome trigger
Troublesome trigger [message #552050] Mon, 23 April 2012 05:48 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Q1)Is this True" trigger can do the check if a record changed without looking up in the database table,It can be checked from memory variables in the database user area" ?

If Yes how?


There is a table in which some updates, inserts are happening but due to some reason i cannot put a check on update,insert while using trigger

Is there any other way I dont want to use data base table, a global variable or something like that is preferable


Thanks in advance
Re: Troublesome trigger [message #552053 is a reply to message #552050] Mon, 23 April 2012 06:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The question makes no sense. DB triggers (assuming that's what you are talking about) fire when a row is changed, so they know the record has changed because if it hadn't they wouldn't be executing.

The rest of your post is so vague I have no idea what you want. You need to give clearer details of what you are trying to achieve.
Re: Troublesome trigger [message #552101 is a reply to message #552053] Mon, 23 April 2012 09:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can use a row trigger and compare old and new values.
Re: Troublesome trigger [message #552449 is a reply to message #552101] Wed, 25 April 2012 22:41 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
on a row level trigger:

IF INSERTING then...
elsif UPDATING then...
   if UPDATING('EMPNO') then...
      if :new.empno != :old.empno or
         :new.empno is null and :old.empno is not null or
         :new.empno is not null and :old.empno is null then...
      end if;
   elsif UPDATING('ENAME') then...
   end if;
else ... (must be deleting)
end if;
the above ask various questions

--
-- are we inserting
-- are we updating
--    was column XYZ updated
--       did the value actually change in the updated column
-- must be deleting
--
unfortunately, I do not believe an INSERT will tell you if you have specified a specific column or not in a similar fashion that the update will tell you if you touchec a specific column. for example:

update T1 set a = 1, B = B;
update T1 set a = 1;
do the same thing, but you can tell inside the trigger if B was actually SET in the update by using the expression IF UPDATING('B'), (even though the value has not changed).

But for an insert, you cannot tell the difference between:

insert into T1 (a,b) values (1,null);
insert into T1 (a) values (1);
there is no INSERTING('B') type of syntax like there was with the update. Seems like a mistake to me and there should be. Maybe someone knows better?

Kevin
Previous Topic: Query on the performance of JOIN on indexed columns
Next Topic: Tuneup the SQL
Goto Forum:
  


Current Time: Thu Mar 28 09:37:48 CDT 2024