| trgger for date [message #9298] |
Sat, 01 November 2003 23:12 |
wayne
Messages: 27
Registered: March 2001
|
Junior Member |
|
|
Dear friend,
How to take the collection Date minus the system date, then get the number and minus the Lead Time ??
I had done, but no effect when I insert data into cakebookingExp table......
create table Cake
( cakeNo varchar2(6) constraint pk_Cake primary key,
cakeName varchar2(30),
unitPrice number(6,2) constraint check_cakePrice check(UnitPrice is not null and UnitPrice > 0),
cake_size varchar2(10),
cake_type varchar2(25),
lead_time number(3) constraint check_cake_completetime check(lead_time is not null and lead_time > 0)
);
create table cakeBooking
( cakebookNo number(4) constraint pk_cakeBook primary key,
customerID varchar2(6) not null constraint fk_cakeBook_customerID references customer(customerID),
custName varchar2(15)
);
create table cakeBookingExp
( cakebookNo number(4) constraint fk_cakeBookExp_cakeBookNo references cakeBooking(cakeBookNo),
cakeNo varchar2(6) constraint fk_cakeBookExp_cakeNo references cake(cakeNo),
bookDate date,
collectionDate date,
constraint pk_cakeBookExp primary key(cakeBookNo, cakeNo)
);
SQL> CREATE OR REPLACE TRIGGER DateChecking
2 AFTER INSERT OR UPDATE OF COLLECTIONDATE ON CAKEBOOKINGEXP
3 FOR EACH ROW
4
5 DECLARE
6
7 CollDate number;
8 LT number;
9
10 BEGIN
11 collDate := TRUNC((:NEW.collectionDate - sysDate)/365.25);
12 select lead_time into LT from cake
13 where cakeNo = :NEW.cakeNo;
14 IF (collDate != LT ) THEN
15 DBMS_OUTPUT.ENABLE;
16 DBMS_OUTPUT.PUT_LINE('INCORRECT ENTRY');
17 ELSE
18 DBMS_OUTPUT.ENABLE;
19 DBMS_OUTPUT.PUT_LINE('CORRECT');
20 END IF;
21 END;
22 /
Trigger created.
SQL> select * from cake where cakeno = 'CK012l';
CAKENO CAKENAME UNITPRICE CAKE_SIZE CAKE_TYPE LEAD_TIME
------ ------------------------------ --------- ---------- ------------------------- ---------
CK012l Ho Ho Cake 48 large Wedding Cake 6
SQL> insert into cakeBookingExp values (0001, 'CK012l', '21-OCT-03', '30-OCT-03');
1 row created.
SQL>
SQL> delete from cakebookingexp where cakeno ='CK012l';
1 row deleted.
SQL> set serveroutput on
SQL> insert into cakeBookingExp values (0001, 'CK012l', '21-OCT-03', '30-OCT-03');
INCORRECT ENTRY
INCORRECT ENTRY
1 row created.
SQL>
SQL> delete from cakebookingexp where cakeno ='CK012l';
1 row deleted.
SQL> insert into cakeBookingExp values (0001, 'CK012l', '21-OCT-03', '29-OCT-03');
INCORRECT ENTRY
1 row created.
SQL> delete from cakebookingexp where cakeno ='CK012l';
1 row deleted.
SQL> insert into cakeBookingExp values (0001, 'CK012l', '21-OCT-03', '31-OCT-03');
INCORRECT ENTRY
1 row created.
SQL> delete from cakebookingexp where cakeno ='CK012l';
1 row deleted.
SQL> insert into cakeBookingExp values (0001, 'CK012l', '21-OCT-03', '30-OCT-03');
INCORRECT ENTRY
1 row created.
How ? The prompt out message is kept on INCORRECT, why ?
Thank you for your help......Thank you
thankyou,
wayne
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
