CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
avid_live_code
avid_live_code

Reputation: 23

Creating trigger in PLSQL--Receiving Error Message

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!

Upvotes: 0

Views: 428

Answers (1)

APC
APC

Reputation: 146189

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';

Upvotes: 2

Related Questions