Prior to release 11.0, a correlated expression subquery in the set clause of an update returned 0 instead of NULL when there were no matching rows. Release 11.0 correctly returns NULL when there are no matching rows, and an error is raised if the column does not permit nulls.
If you have applications that depend on the pre-release 11.0 behavior, you will need to rewrite them.
For example, the following trigger tries to update a column that does not permit NULL values:
update t1
set c1 = (select max(c1)
from inserted where t1.c2 = inserted.c2)
The correct trigger is:
update t1
set c1 = (select isnull(max(c1), 0)
from inserted
where t1.c2 = inserted.c2)
or:
update t1
set c1 = (select max(c1)
from inserted
where t1.c2 = inserted.c2)
where exists (select * from inserted
where t1.c2 = inserted.c2)
The where clause updates table t1.c1 to 0, if the subquery does not return any correlation values from the outer table t1.
Another example of this is the totalsales_trig in the pubs2 sample database. In previous versions, the trigger read as follows:
create trigger totalsales_trig
on salesdetail
for insert, update, delete
as
/* Save processing: return if there are no rows affected */
if @@rowcount = 0
begin
return
end
/* add all the new values */
/* use isnull: a null value in the titles table means
** “no sales yet” not “sales unknown”
*/
update titles
set total_sales = isnull(total_sales, 0) +
(select sum(qty)
from inserted
where titles.title_id = inserted.title_id)
/* remove all values being deleted or updated */
update titles
set total_sales = isnull(total_sales, 0) -
(select sum(qty)
from deleted
where titles.title_id = deleted.title_id)
sum(qty) is NULL if no row is returned from the table, so when a statement changes the total_sales column, the trigger changes to NULL all the rows in titles that do not qualify.
To guarantee that the subquery in the expression for the update returns a non-null value, the corrected trigger is:
create trigger totalsales_trig
on salesdetail
for insert, update, delete
as
/* Save processing: return if there are no rows affected */
if @@rowcount = 0
begin
return
end
/* add all the new values */
/* use isnull: a null value in the titles table means
** “no sales yet” not “sales unknown”
*/
update titles
set total_sales = isnull(total_sales, 0) +
(select sum(qty)
from inserted
where titles.title_id = inserted.title_id)
where title_id in (select title_id from inserted)
/* remove all values being deleted or updated */
update titles
set total_sales = isnull(total_sales, 0) -
(select sum(qty)
from deleted
where titles.title_id = deleted.title_id)
where title_id in (select title_id from deleted)
