[deepangunalan]

hi,
i want create a trigger when i update a reservation table ..it should triggger a insert function on to the charge table.i have tried ...a code ..which is not working ...tell me how to deal with this....

i am sure ...syntax which is used not correct..so i need ur help for fire this trigger


create or replace trigger Credit_Card_Charge
AFTER UPDATE on reservation
for each row
declare begin

select reservationid from reservation
where
(floor ((startdatetime-cancellationdatetime )*24) -
(floor(startdatetime-cancellationdatetime)*24)) < 48;
// as u check ...as per condition ..i am finding reservation id ...now i
// have to
// insert values into charge table ..with date ...category--//'fine',charge,and
//reservation id...how to enter reservation value into charge table got
//from reservation
//table....

insert into charges values(TO_DATE('12/09/2003',
'MM/DD/YYYY-HH24-MI-SS''),'FINE',50.00,4);



end;
/


thanx..cya....

[Hings]

First of all, let me clear your problem.

as per your code, the value returned by the WHERE clause will always be 0 because you r substracting the same value. Obviously, your code is syntectically wrong.


"(floor ((startdatetime-cancellationdatetime )*24) -
(floor(startdatetime-cancellationdatetime)*24)) < 48"

I guess either of them shoud be :NEW and :OLD as shown following. I don't know your table data. but you can figure it out.


"(floor ((:OLD.startdatetime-:OLD.cancellationdatetime )*24) -
(floor(:NEW.startdatetime-:NEW.cancellationdatetime)*24)) < 48"

So with correction, it shoud look like this.

CREATE OR REPLACE TRIGGER Credit_Card_Charge
AFTER UPDATE ON reservation
FOR EACH ROW
DECLARE
v_temp VARCHAR2 (50);
BEGIN
SELECT reservationid //provided if it returns only one value.
INTO v_temp // otherwise you can use cursor or TYPE.
FROM reservation
WHERE
(FLOOR ((:OLD.startdatetime-:OLD.cancellationdatetime )*24) -
FLOOR ((:NEW.startdatetime-:NEW.cancellationdatetime)*24)) <
48;
INSERT INTO charges VALUES (TO_DATE('12/09/2003',
'MM/DD/YYYY-HH24-MI-SS'),'FINE',50.00,4);
END;
/

[Hings]

I forgot to mention that you can also include exception handler in your code.

[deepangunalan]

Originally posted by Hings
I forgot to mention that you can also include exception handler in your code.

hi,

i will explain about my table

we are designing table for hotel reservation:

i have a table --->
reservation containing following attributes:

reservationid,hotelphone,startdate,nights,guestpho ne,creditcard_num,
checkindate,checkoutdate,cancellationdate,roomnumb er

charges table containing following attributes

chargesdate,category(like FINE OR ROOM CHARGES),charge_amount,
reservation_id

so i told earlier....i have triggering if person cancel ...before startdate
time ie 48 hrs before he has to cancel...otherwise ..we have to charge...

that is y ...i am selecting reservationid from reservation table...where i am finding out ...(i am finding out whether people have cancelled before 48 hrs or not....

for this only i have used this function....

(floor ((startdatetime-cancellationdatetime )*24) -
(floor(startdatetime-cancellationdatetime)*24)) < 48;

using that reservationid ..i am going to insert values into charge table.....
chargedate,category,amount,reservationid..

i have already got a idea about the trigger..but i have given u all details about my table..could give me idea how to do that...
wat is NEW and OLD..when they will be used...


thanxx...pls give me a clear idea....

[Hings]

You mean to say that if someone cancels reservation before 48 hours of startdate, than it is okey, otherwise you have to charge them. So
do following.

CREATE OR REPLACE trigger xxx
AFTER UPDATE OF cancellationdate ON reservation
FOR EACH ROW
DECLARE
v_flag NUMBER;
BEGIN
SELECT 1
FROM reservation
WHERE :OLD.cancellationdate IS NULL;
IF (startdate - :NEW.cancellationdate) > 48 THEN
INSERT INTO charges VALUES
(TO_DATE('12/09/2003', 'MM/DD/YYYY-HH24-MI-S'),'FINE',50.00,4);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/

I guess this would help you out. I haven't checked it syntectically so plese correct it if it has any syntex error.

For :OLD and :NEW, please read oracle documentation.

[Hings]

I am sorry,

it should be " < 48 " instead of > 48. please correct it.