July 13, 2004 at 11:54 pm
I recently stumbled across this problem in a commecial back-end database. I have simplified the problem in a small script. Is this a known bug? There are many non-elegant ways of overcoming this problem, but I haven't dicovered a nice solution that lets me update (update AAAA_VIEW_TEST SET ....) rows with null in F4 via the view. I can include logic in the trigger to turn an insert statement into an update, but that is not the aim.
BOL say about INSTEAD OF UPDATE Triggers:
"In the inserted table passed to an INSTEAD OF UPDATE trigger, the columns specified in the SET clause follow the same rules as the inserted columns in an INSTEAD OF INSERT trigger. For columns not specified in the SET clause, the inserted table contains the values as they existed before the UPDATE statement was issued".
The script below demonstrates the problem. The update statement which explicitly sets non-null values for each view column does not produce a row in table inserted, but the insert statement does. I can understand why table deleted remains empty. The old record does not satisfy the condition of "F4 is not null". According to my interpretation of BOL both update statements should produce a row in table inserted because the updated (new) row satisfies the view condition.
There is potentially a timing problem. Instead Of triggers are before statement triggers, they kick in before the update statement affects the view. The same however is true for the insert statement.
Thanks for any comments,
Win
--create test table
if exists (select * from dbo.sysobjects where id = object_id(N'[AAAA_TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [AAAA_TEST]
GO
CREATE TABLE [AAAA_TEST] (
[F1] [varchar] (5) NOT NULL ,
[F2] [varchar] (5) NULL ,
[F3] [varchar] (5) NULL ,
[F4] [varchar] (5) NULL ) ON [PRIMARY]
GO
ALTER TABLE [AAAA_TEST] ADD
CONSTRAINT [PK_AAAA_TEST] PRIMARY KEY CLUSTERED
(
[F1]) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------
--create test view with instead-of trigger
if exists (select * from dbo.sysobjects where id = object_id(N'[T_TEST_IU]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [T_TEST_IU]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[AAAA_VIEW_TEST]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [AAAA_VIEW_TEST]
GO
CREATE VIEW AAAA_VIEW_TEST(V1,V2,V3) AS
SELECT F1,F2,F4 FROM AAAA_TEST WHERE F4 IS NOT NULL
GO
CREATE TRIGGER T_TEST_IU
on AAAA_VIEW_TEST
INSTEAD OF INSERT, UPDATE
AS
print cast(cast(COLUMNS_UPDATED() as int) as varchar)
PRINT 'OLD ROW:'
SELECT * FROM DELETED
PRINT 'NEW ROW:'
SELECT * FROM INSERTED
GO
----------------------------------------------------------------------------------------------------------
--test transactions
--populate native table
insert into AAAA_TEST(F1,F2) values('a0','b0')
insert into AAAA_TEST(F1,F2,F4) values('a1','b1','c1')
--read table through view
print '*** Select from view'
select * from AAAA_VIEW_TEST
--transactions on view
print '*** Updating view'
update AAAA_VIEW_TEST set V3='c0' where V1='a0'
update AAAA_VIEW_TEST set V1='a0',V2='b0', V3='c0' where V1='a0'
print '*** Inserting into view'
insert into AAAA_VIEW_TEST(V1,V2) values('a0','b0')
insert into AAAA_VIEW_TEST values('a0','b0','c0')
July 14, 2004 at 12:50 am
I have to correct myself. This is not a bug. The update statement must fail on the row where F4 is null. From the view's perpective this row does not exist. It is like updating an empty table.
Cheers,
Win
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply