Create Trigger with max amount of days 1

[mrasad]

Is it possible to create a trigger that will not allow for an Insert or Update if the due date is greater then 2 weeks from the current date?

I have a loans table;

create table loan
(LoanID varchar2(6) PRIMARY KEY,
Issue_Date date not null,
Due_Date date not null,
......

The issue_date will use the sysdate, and the due date should not be greater then 2 weeks.

Is it also possible to extend that trigger to when loan_id is like "VID%" the the return date should not be over one week?

Any help would be great

Thank you
Asad

 

[carp]

Asad -
Try this:

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW
BEGIN
IF new.loanid LIKE 'VID%'
AND :new.due_date - sysdate > 7) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 7-DAY LIMIT!');
ELSIF new.due_date - sysdate > 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 14-DAY LIMIT!');
END IF;
END;
/

Elbert, CO
0824 MST

 

[mrasad]

Thanks for that, having abit of trouble with that code. I thought, to make life easy I would break down the code, so this is what I done (only cheeking so that items have a borrowing item of 14 days;

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW
BEGIN
IF new.due_date - sysdate > 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS

14-DAY LIMIT!');
END IF;
END;
/

I get

Then I try to insert the following values into the table;

insert into loan
values('L00021',
sysdate,
to_date('30-12-04 23:59','DD-MM-YYYY HH24:MI'),
to_date(''),
'VID006',
'0-330-41914-5',
10);

I wrote 30-12-04 for the due and and sysdate for the issues date and i was expecting that not to work, but it did.

I thought if i changed the sysdate (in the trigger) to

IF new.due_date - :new.Issue_Date > 14) ....

it might work, but no luck ... any suggestions?

 

[carp]

Shouldn't
to_date('30-12-04 23:59','DD-MM-YYYY HH24:MI')
be
to_date('30-12-2004 23:59','DD-MM-YYYY HH24:MI')
?

The way you have it coded, the year of your due date is 0004, which is almost 2000 years ago. Therefore, it does not violate your condition!

 

[mrasad]

Your right

I have another question. I made a mistake in my 1st post where I said loanid maybe like "vid%", however that is not the case. I should have said it was for the itemid (a foreign key from another table).

I have tried to change the code, so it reads like this;

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW
BEGIN
IF new.itemid LIKE 'VID%'
AND :new.due_date - sysdate > 7) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 7-DAY LIMIT!');
ELSIF new.due_date - sysdate > 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 14-DAY LIMIT!');
END IF;
END;

But i get a error;

Warning: Trigger created with compilation errors.

SQL> show errors;
Errors for TRIGGER LOAN_CHECK:

LINE/COL ERROR
2/8 PLS-00049: bad bind variable 'NEW.ITEMID'

i've tried to take :new. out of the code but I still get an error message.

Thanks for your help

 

[carp]

Do you really want to compare the due date to today or to the issue date? If the former, then proceed with the original code. If the latter, then you might change the trigger to something like:

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW
BEGIN
IF new.loanid LIKE 'VID%'
AND (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)) > 7) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 7-DAY LIMIT!');
ELSIF (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)> 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 14-DAY LIMIT!');
END IF;
END;
/

Now - to address your latest problem....

If the value is in another table, you will have to run a query on the table to get the value. :new and ld refer to values within the table being inserted/updated.

So, let's say that you can relate the loan table to the item table via a column called item_name (that is, both tables have a column called item_name). Then the trigger might look something like:

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW

DECLARE
l_item_id VARCHAR2(20);

BEGIN
SELECT item_id INTO l_item_id
FROM items
WHERE item_name = NVLnew.item_name, ld.item_name);

IF (l_item_id LIKE 'VID%'
AND (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)) > 7) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 7-DAY LIMIT!');
ELSIF (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)> 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 14-DAY LIMIT!');
END IF;

END;
/

 

[mrasad]

>>"If the value is in another table, you will have to run a query on the table to get the value.

So, let's say that you can relate the loan table to the item table via a column called item_name (that is, both tables have a column called item_name)"


By the above do you mean, i need to insert to more fields in both the loan and av_item table? Sorry i'm not quite sure, the reason I ask is that I need to do create similar triggers on other tables.

Below are the table for me loan and av_items;

AV_ITEM

create table AV_Item
(Item_ID varchar2(7) PRIMARY KEY,
Item_Type varchar2(10) not null,
Item_Title varchar2(50) not null,
Item_Copies number(3) not null,
Item_Genre varchar2(10),
Item_Distributor varchar2(10),
Release_Date Date,
main_Performer varchar2(20),
Item_director varchar2(20),
Item_Certificate varchar(3) not null);

(example values
insert into AV_Item
values
('PS2001',
'GAME',
'PRO EVOLUTION SOCCER 3',
'04',
'SPORT',
'KONAMI',
'20-JUN-03',
'',
'',
'4+');
)


LOAN

create table loan
(LoanID varchar2(6) not null primary key,
Issue_Date date not null,
Due_Date date not null,
Return_Date date not null,
Item_ID varchar2(6),
BookNo varchar2(13),
MemberID number(7) not null);


(Example values
insert into loan
values('L00001',
to_date('04-JUN-2003 14:20:00','DD-MM-YYYY HH24:MI:SS'),
to_date('18-06-2003 23:59','DD-MM-YYYY HH24:MI'),
to_date('20-06-2003 16:59','DD-MM-YYYY HH24:MI'),
null,
'0-261-10273-7'
,1);
)

 

[carp]

Oh! So itemID is in the loan table?! That certainly simplifies things! If I'm understanding your requirements correctly, then this should do the trick:

CREATE OR REPLACE TRIGGER loan_check
BEFORE INSERT OR UPDATE ON loan
FOR EACH ROW
BEGIN
IF new.item_ID LIKE 'VID%'
AND (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)) > 7) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 7-DAY LIMIT!');
ELSIF (NVLnew.due_date,ld.due_date)-NVLnew.issue_date,ld.issue_date)> 14) THEN
RAISE_APPLICATION_ERROR(-20001,'DUE DATE EXCEEDS 14-DAY LIMIT!');
END IF;
END;


One question though - if item_id is a varchar2(7) in one table, and you want a foreign key relationship between the tables, shouldn't item_id be a varchar2(7) in the loan table (instead of a varchar2(6))?

 

[mrasad]

It works!!! Thanks mate. By accident I copied the wrong table, that was an old table I had but has since been updated.

Sorry if I was not clear in my post, I suffer for dyslexia and find it really hard to express what I want to say in writing. I'll try harder next time, promise.

Happy new year to you, and everyone at tek-tips.

 

[carp]

Glorious news - we end the year with one less problem!
And a happy new year to you!