OraFAQ Forum: Performance Tuning » Troublesome trigger

Home » RDBMS Server » Performance Tuning » Troublesome trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Troublesome trigger [message #552050]

Mon, 23 April 2012 05:48

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

Report message to a moderator

Re: Troublesome trigger [message #552053 is a reply to message #552050]

Mon, 23 April 2012 06:03

cookiemonster
Messages: 13962
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.

Report message to a moderator

Re: Troublesome trigger [message #552101 is a reply to message #552053]

Mon, 23 April 2012 09:25

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

You can use a row trigger and compare old and new values.

Report message to a moderator

Re: Troublesome trigger [message #552449 is a reply to message #552101]

Wed, 25 April 2012 22:41

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 14:17:08 CST 2025