DB2SQL Trying to create a trigger with function (Calculate days)

Question

So for now, I created 2 tables, Booking and BookingDetails, I want to create a trigger when I key in the details of BookingDetails it will automatically update totaldays inside the Booking table. Below is my code:

BookingDetails:

create table BookingDetail ( 
BD_ID int primary key not null, 
Date_In date, 
Date_Out date, 
BK_ID int, 
Room_ID int,
foreign key(BK_ID) references Booking(BK_ID),  
foreign key(Room_ID) references Room(Room_ID)
)

And also Booking

create table Booking ( 
BK_ID int primary key not null, 
BK_Date Date, 
BK_TotalDays int, 
BK_PayStatus char(6), 
Cus_ID int, 
Emp_ID int,
foreign key(Cus_ID) references customer(Cus_ID),  
foreign key(Emp_ID) references Employee(Emp_ID)
)

With the function and trigger created:

create function countdays(t1 date, t2 date)
returns INT
return (timestampdiff(16, char(timestamp(t2) - timestamp(t1))))

create trigger totaldays
after insert on bookingdetail
referencing new as n
for each row mode db2sql
update booking
set bk_totaldays = 
countdays((select date_in from bookingdetail), (select date_out from 
bookingdetail))
where booking.bk_id = n.bk_id;

I have no problem executing these syntax, but when I try to input a new record inside Booking Detail to let the trigger triggers in Booking, errors occured, may I ask why? Thanks in advance.

Using db2 on Windows, the error output "DB2 SQL-Error: -811" — Jovix, Oct 01 '17 at 10:18
**WARNING!!** The result of the timestamp subtraction [is an estimate](https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzscatimedifstmp.htm). For example, `timestamp('2017-02-01', '00:00:00') - timestamp('2017-01-01', '00:00:00')` yields `100,000,000.000000` (or "1 month"), so would give back **30** days, when the correct answer is **31** days. _This is almost certainly not what you want._ [Use `DAYS()` for more accurate results](https://stackoverflow.com/a/9541845/812837). — Clockwork-Muse, Oct 02 '17 at 16:40

score 1 · Accepted Answer · answered Oct 01 '17 at 10:21

1

Look at the information provided by the SQL error:

db2 ? SQL0811
SQL0811N  The result of a scalar fullselect, SELECT INTO statement, or
  VALUES INTO statement is more than one row.

So this part of your trigger expressions returns more than 1 row

set bk_totaldays = countdays((select date_in from bookingdetail), 
                             (select date_out from bookingdetail))

Fix this to return a single row.

answered Oct 01 '17 at 10:21

MichaelTiefenbacher

3,805
2
11
17

But how can I fix this by return it to single row so that in my countday functions, are able to calculate by receiving 2 dates as input? – Jovix Oct 01 '17 at 10:29
Well you can receive 2 values but just 1 row - you hav eto correlate the selects to the current insert. At the moment your select date_in from bookingdetail will select as many rows as the bookingdetail table holds. So the function does not know which row / value is the right one. – MichaelTiefenbacher Oct 01 '17 at 10:32
From here I try to fix by using this way to select in just 1 line"set bk_totaldays = countdays((select date_in, date_out from bookingdetail where booking.bk_id = n.bk_id))" but it still gave me an error. – Jovix Oct 01 '17 at 10:41
Right track - but bk_id is the primary key of your booking table so there could be still many rows returned as you select the bookingdetails table. Try adding "fetch frist 1 row only" – MichaelTiefenbacher Oct 01 '17 at 10:44
I success to execute by adding "Fetch first 1 row only" command, Thanks! But there is a problem here, what if I have multiple values? I tried to insert a few data in but only the first row has the function working while other values remains the same value as first row. – Jovix Oct 01 '17 at 11:02
Ok - well fetch first 1 row is only an example any other extension to your query would do the job if it limits the resultset to a single row - you could select the "last" detail row or the one with the newest timestamp (if it exists). Please do not forget to mark my answer as solution - thanks – MichaelTiefenbacher Oct 01 '17 at 11:08

score 0 · Answer 2 · answered Oct 02 '17 at 16:59

@MichaleTiefenbacher is correct about the cause of the error, but he's wrong about what to do about it. Let's take another look at your trigger again:

create trigger totaldays
after insert on bookingdetail
referencing new as n -- wait, what's this?
for each row mode db2sql
update booking
set bk_totaldays = 
countdays((select date_in from bookingdetail), (select date_out from bookingdetail))
where booking.bk_id = n.bk_id;

You have a reference to the value you just inserted! When using FOR EACH ROW, the table reference NEW refers to the singular row just inserted. So you don't even need to look at the full table, just use what you just worked with:

create trigger totaldays
after insert on bookingdetail
referencing new as n 
for each row mode db2sql
update booking
set bk_totaldays = countdays(n.date_in, n.date_out)
where booking.bk_id = n.bk_id;

(As I mentioned in my comment to your question, you'll likely want to change how you calculate the days, but that's irrelevant for this)

DB2SQL Trying to create a trigger with function (Calculate days)

2 Answers2