Creating trigger in PLSQL--Receiving Error Message

Question

I am creating a trigger that for a ticket table that is activated when the status of the ticket goes from not paid (3) to paid (4). I also have to print out a message saying ticket A is paid on B, where A is ticket ID and B is the payment date.

So far, I have been unable to successfully print out the date when the ticket is paid.

My progress so far:

create or replace trigger change_ticket_status
after update on car_ticket
for each row when (new.status<> old.status)
begin
dbms_output.put_line('Ticket status is ' ||:new.status || ' (paid).' );
dbms_output.put_line('Ticket ID' || :new.tid || ' was paid on' || :new.paydate);
end;

testing it:

 update car_ticket
set status=2
where tid=4 and paydate= '2018-11-24';

car_ticket table:

(tid int, --- parking ticket id
cid int, --- the car that violated parking regulations
lid int, --- lot where violation took place
ttype int, -- ticket type
status int, --- 3 issued, 4 paid, 
tdate date, --- ticket issue date
paydate date, --- date ticket is paid
note varchar(50), --- notes about the violation
primary key(tid), 
foreign key(cid) references car,
foreign key (ttype) references ticket_type,
foreign key (lid) references lot
);

Result:

Trigger Created

Result for Testing:

ORA-01861: literal does not match format string
ORA-06512: at "SYS.DBMS_SQL", line 1721

Your help is greatly appreciated!

The error references SYS.DBMS_SQL - are you using dynamic SQL? And if so, why? — APC
– APC, Commented Nov 24, 2018 at 7:13

APC · Accepted Answer · 2018-11-24 16:46:55Z

2

ORA-01861: literal does not match format string

This happens when we try to cast a sting to a date and Oracle doesn't recognise the format of the string. So it will be this line here:

 where tid=4 and paydate= '2018-11-24';

It looks like you're trying to use an ASCII date literal but you're missing the date keyword. Try this:

where tid=4 and paydate= date '2018-11-24';

I ran the update and no rows are updating

That suggests you have no records which match those criteria. If you're expecting rows to be updated I suggest you have a look at the precise values of paydate. Oracle DATE datatypes contain a time element: if you have been populating your table with (say) sysdate you will have dates with time elements which don't match date '2018-11-24' (midnight).

In this case you should change the WHERE clause to allow for time:

where tid=4 
and paydate >= date '2018-11-24' 
and paydate < date '2018-11-25';

edited Nov 24, 2018 at 16:46

answered Nov 24, 2018 at 7:10

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

avid_live_code Over a year ago

Hello! Thanks for the feedback. I ran the update and no rows are updating. Comment from Oracle: 0 row(s) updated.

APC Over a year ago

So at least I answered your original question ;)

Collectives™ on Stack Overflow

Creating trigger in PLSQL--Receiving Error Message

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related