Home » SQL & PL/SQL » SQL & PL/SQL » :NEW trigger issue (Oracle DB, 10g r2, Red Hat 4)
:NEW trigger issue [message #351072] Mon, 29 September 2008 03:32 Go to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi, I have a problem in one of my triggers:

I have a table which has 2 fields namely; CARD_USE and CARD_USE_LIMIT, in one procedure I have initialized the CARD_USE_LIMIT := 99, and I I have defined if the CARD_USE reaches to 99 the Card will be blocked. so now I want to add an IF condition to my trigger that if the value of CARD_USE reaches to 99 , then initialize it to 0.

that is not a problem. I can write something like this:

IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := 0;

or

IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := :OLD.card_use;

this is working... but it works only in my Database environemnt, Meaning when I use update statement to assign a any value, the card_use will be changed to 0 with no problem. that is fine, but this changes will not be applied to my application, when i run my code(C Program code) It will still increase the value of CARD_USE and when it goes to 99 the card will be blocked!

I dun know what is wrong, any hint will be appreciated.

here is my full code for my "trigger1" on my "table1" with the user name "User".
-- ------------------------------------------------------
CREATE OR REPLACE TRIGGER User.Table1_Trigger1
BEFORE DELETE OR INSERT OR UPDATE
ON User.Table1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

BEGIN
...

IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := 0;
END IF;
...

END;
-- ------------------------------------------------------
Re: :NEW trigger issue [message #351075 is a reply to message #351072] Mon, 29 September 2008 03:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please read posting guidelines about how to format
your post.

Regards,
Rajat
Re: :NEW trigger issue [message #351078 is a reply to message #351075] Mon, 29 September 2008 04:05 Go to previous messageGo to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi, I have a problem in one of my triggers:

I have a table which has 2 fields namely; CARD_USE and CARD_USE_LIMIT, in one procedure I have initialized the CARD_USE_LIMIT := 99, and I I have defined if the CARD_USE reaches to 99 the Card will be blocked. so now I want to add an IF condition to my trigger that if the value of CARD_USE reaches to 99 , then initialize it to 0.

that is not a problem. I can write something like this:
IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := 0;

or
IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := :OLD.card_use;

this is working... but it works only in my Database environemnt, Meaning when I use update statement to assign a any value, the card_use will be changed to 0 with no problem. that is fine, but this changes will not be applied to my application, when i run my code(C Program code) It will still increase the value of CARD_USE and when it goes to 99 the card will be blocked!

I dun know what is wrong, any hint will be appreciated.

here is my full code for my "trigger1" on my "table1" with the user name "User".
-- ------------------------------------------------------
CREATE OR REPLACE TRIGGER User.Table1_Trigger1
BEFORE DELETE OR INSERT OR UPDATE
ON User.Table1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

BEGIN
...

IF NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
:NEW.card_use := 0;
END IF;
...

END;
-- ------------------------------------------------------ 

[Updated on: Mon, 29 September 2008 04:09]

Report message to a moderator

Re: :NEW trigger issue [message #351084 is a reply to message #351078] Mon, 29 September 2008 04:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

this is working... but it works only in my Database environemnt, Meaning when I use update statement to assign a any value, the card_use will be changed to 0 with no problem. that is fine, but this changes will not be applied to my application, when i run my code(C Program code) It will still increase the value of CARD_USE and when it goes to 99 the card will be blocked!



You are using row level trigger that is fired for each row.
Please clarify what opeartion you are doing.You tested it for
update.

Are you getting problem in insert??

Please clarify more.

Regards,
Rajat

Re: :NEW trigger issue [message #351086 is a reply to message #351084] Mon, 29 September 2008 04:36 Go to previous messageGo to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi, hanks for the reply

Yes. actually I am getting the problem in insert, while the application is runnng it is inserting the values into card_use field.

due to the number of transactions in one part of the application the card_use will be increased accordingly.

Now, the problem is that I want to stop this increase. because by default the transactions are not allowed when the card_use is reached to 99.

Due to some testing requirements I need to do a high traffic transactions, more than even 500 transactions, so I need to write a trigger to initialize the CARD_USE to 0 after reaching to 99. this can be happened using my code, but does not happen while my app is working, simply saying I assume the trigger condition does not get satisfied(I guess it does not go through the condition)

 IF  NVL(:OLD.card_use, 0) <> NVL(:NEW.card_use, 0) THEN
        :NEW.card_use := 0;
 END IF;


Regards
meysam

[Updated on: Mon, 29 September 2008 04:37]

Report message to a moderator

Re: :NEW trigger issue [message #351095 is a reply to message #351086] Mon, 29 September 2008 04:58 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
When doing insert thier is no old value.

Thier is only new value.

OLD value comes in play when you modifying a row.

So condition fails.

Regards,
Rajat
Re: :NEW trigger issue [message #351104 is a reply to message #351095] Mon, 29 September 2008 05:11 Go to previous messageGo to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi, Thanks for the reply,

I tried also putting a separate condition like this:
IF :NEW.CARD_USE <= 100 THEN
   :NEW.CARD_USE := 0;
  END IF;


but still it does not work. do you have anything to add my code?

Best regards
Meysam
Re: :NEW trigger issue [message #351109 is a reply to message #351104] Mon, 29 September 2008 05:33 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I am still not clear what you actually required.

But this condition will set CARD_USE value to 0 when ever the
value is less then and equal to 100.

But what i think that you need to set it to 0 when it reaches 99.

You said that 500 different transactions are doing manipulation.
and each transaction can only insert upto 99 rows.

How would you know which transaction has incremented the value
because trigger don't have idea which transaction is changing
the value of CARD_USE.

What you require is not clear.
Quote:

I have a table which has 2 fields namely; CARD_USE and CARD_USE_LIMIT, in one procedure I have initialized the CARD_USE_LIMIT := 99, and I I have defined if the CARD_USE reaches to 99 the Card will be blocked. so now I want to add an IF condition to my trigger that if the value of CARD_USE reaches to 99 , then initialize it to 0.



What you wan't to say by this.You are initializing a variable
checking it in trigger.why you need trigger for that.


You have provided unclear information which goes no where.

Regards,
Rajat







Previous Topic: how to return result with more than one distinct record
Next Topic: Ned to capture timing statistics in a stored procedure
Goto Forum:
  


Current Time: Sat Dec 03 09:51:58 CST 2016

Total time taken to generate the page: 0.05768 seconds