Jovix Jovix - 3 years ago 161
SQL Question

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

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download