instead of trigger on partitioned view 1

[icahn]

In SQL Server 2000, I am trying to horizontally partition data into 2 tables using a check constraint on a datetime column as the partition key. The tables are unioned together by a view. There is no primary key in the base tables, so the view is not updatable. An instead-of-insert trigger is built on the view that inserts into the base tables whenever an attempt is made to insert into the view. The base tables are local (in the same database; not distributed). Details;

use testdb
drop table a
CREATE TABLE a (
ClientUserName varchar (32) NOT NULL ,
logTime datetime NOT NULL CHECK (datepart(wk,logtime)=1 OR datepart(wk,logtime)=2)
)

drop table b
CREATE TABLE b (
ClientUserName varchar (32) NOT NULL ,
logTime datetime NOT NULL CHECK (datepart(wk,logtime)=3 OR datepart(wk,logtime)=4)
)

create view ab as
SELECT clientusername,logtime FROM a
UNION ALL
SELECT clientusername,logtime FROM b

create trigger abtrig on ab instead of insert
as
if exists( select * from inserted where datepart(wk, logtime) >4)
begin
raiserror('Bad record', 16,1)
rollback transaction
end
else
begin
if exists (select * from inserted where
datepart(wk,logtime)=1 OR datepart(wk,logtime)=2)
insert into a
select * from inserted

if exists (select * from inserted where
datepart(wk,logtime)=3 OR datepart(wk,logtime)=4)
insert into b
select * from inserted

commit transaction
end
go

From query analyzer, if I insert into the view by running one statement at a time, the partitioning works:

insert ab clientusername, logtime values ('abcd',datepart(wk, getdate() - 50))

select * from a gives one row.
select * from b gives no rows.

But when trying to batch insert several rows at one go;
insert ab clientusername, logtime values ('abcd',datepart(wk, getdate() - 50))
insert ab clientusername, logtime values ('abcd',datepart(wk, getdate() - 45))
insert ab clientusername, logtime values ('abcd',datepart(wk, getdate() - 40))

only the first row is inserted. The other 2 inserts silently fail. What has gone wrong?

 

[sguslan]

Remove the commit transaction out of the trigger and works. You can Begin Tran and End Tran outside the trigger within the application or tool doing the inserts.

I have no explanation. But hope gurus like Terry will enlighten us !

 

[icahn]

Thanks sguslan! Your suggestion works - remove the commit transaction and the batch update goes through.

Would appreciate any tips on commit/rollback in sql server triggers.

 

[tlbroadbent]

If you perform a multi-row insert, the Trigger as posted may not handle the Inserts correctly. Take this query for example.

Insert ab clientusername, logtime
Select UserName, TimeIn
From TransactionTable

Suppose this Insert results in values for weeks 1 thru 4. The Trigger will test if a value exists for 1 or 2 and then attempt insert all the rows into table a. The check constraint will prevent the Insert of invalid rows and no rows will be inserted into table a or table b.

Another matter to consider is what action should occur if one or more rows contain a week > 4 but other rows contain valid week numbers. Currently, the Trigger will reject the entire batch. This may be the appropriate response. Note that I'm not suggesting the appropriate response. I just wanted to point out that you need to be careful to properly handle multiple row row inserts in Triggers. I'd change the Trigger as follows in order to handle the multi-row inserts.

create trigger abtrig on ab instead of insert
as

if exists( select * from inserted where datepart(wk, logtime) >4)
begin

raiserror('At least one row has an invalid date', 16,1)
rollback transaction

end
else
begin
--The If statements are not necessary because the
--Where clause handles the criteria test.
insert into a
select * from inserted
where datepart(wk,logtime) between 1 and 2

insert into b
select * from inserted
where datepart(wk,logtime) between 3 and 4

end
go

I used Between to show another way to write the criteria. datepart(wk,logtime) = 3 OR datepart(wk,logtime) = 4 is perfectly good code as is datepart(wk,logtime) IN (3,4). If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sguslan]

Thanks Terry ! Prompt as usual ! MAy almighty GOD bless your helping hands !

But Terry , i tested the trigger with the following modifications using a where clause:

if exists (select * from inserted where
datepart(wk,logtime)=1 OR datepart(wk,logtime)=2)
insert into a
select * from inserted
WHERE datepart(wk,logtime)=1 OR
datepart(wk,logtime)=2)

if exists (select * from inserted where
datepart(wk,logtime)=3 OR datepart(wk,logtime)=4)
insert into b
select * from inserted
WHERE datepart(wk,logtime)=3 OR
datepart(wk,logtime)=4)

And ended up with the same results Icahn reported...
I thought adding a where clause would solve the problem of inserting the wrong rows and not upset the Check constraints..

Any thoughts ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[tlbroadbent]

When three inserts are run in succession, the trigger fires for each insert statement. When multiple rows are inserted with one insert statement, the trigger fires once and must be able to handle multiple rows.

The 3 test queries aren't very beneficial for testing. The queries insert the week number of the current date less the number of days indicated. datepart(wk,getdate()-50) returns 3. When the inserted value is 3 it is the same as inserting 1900-01-04 into the datetime column. Evaluting the date 1900-01-04 with datepart using the WK the parameter will return 1. Ithink the following would be a more appropriate test query.

insert ab (clientusername, logtime)
values ('abcd', getdate() - 50)

The following script can be used to test multipl-row inserts.

Declare @tbl Table(TestDateTime datetime)
set nocount on
Insert @tbl(getdate()-10)
Insert @tbl(getdate()-20)
Insert @tbl(getdate()-30)
Insert @tbl(getdate()-40)
Insert @tbl(getdate()-50)
Insert @tbl(getdate()-60)
Insert @tbl(getdate()-70)
Insert @tbl(getdate()-80)

Insert ab (clientusername, logtime)
Select 'abcd', TestDateTime
From @tbl If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[icahn]

Thanks for all the ideas!

1. The sample inserts I originally posted are incorrect as Terry pointed out.
Should be,
Insert ab values ('abdc', getdate()-10)
not,
Insert ab values ('abcd',datepart(wk, getdate() - 50))
Apologies for any confusion caused.

2. In the live system, I'll have 26 base tables - one for each fortnight - so there wouldn't be invalid weeks from datepart (wk, logtime). So the first "if" condition at the top of the trigger can be removed.

3. The behaviour I originally encountered where only the first insert is successful is caused by a missing "begin transaction" in the trigger. When this is included, the commit works as expected.

4. Even with the commit in the trigger,the calling application (I'm just using query analyzer) can still rollback the inserts.

Using the same tables and views as before, the new trigger is,

create trigger abtrig on ab instead of insert
as
begin
begin tran T1
insert into a
select * from inserted where
datepart(wk,logtime)=1 OR datepart(wk,logtime)=2

insert into b
select * from inserted where
datepart(wk,logtime)=3 OR datepart(wk,logtime)=4

commit tran T1
end
go

Doing multiple inserts 2 different ways:

insert ab values ('xxxx', getdate()-40)
insert ab values ('xxxx', getdate()-50)
insert ab values ('xxxx', getdate()-55)
insert ab values ('xxxx', getdate()-63)

Declare @tbl Table(TestDateTime datetime)
set nocount on
Insert @tbl values (getdate()-40)
Insert @tbl values (getdate()-50)
Insert @tbl values (getdate()-55)
Insert @tbl values (getdate()-63)

Insert test_mgr.ab (clientusername, logtime)
Select 'abcd', TestDateTime
From @tbl


select * from a
select * from b

ClientUserName logTime
----------------- ------------------------
xxxx 2003-01-10 22:44:36.197
xxxx 2003-01-02 22:44:36.197
abcd 2003-01-10 22:44:36.197
abcd 2003-01-02 22:44:36.197

ClientUserName logTime
----------------- -------------------------
xxxx 2003-01-25 22:44:36.197
xxxx 2003-01-15 22:44:36.197
abcd 2003-01-25 22:44:36.197
abcd 2003-01-15 22:44:36.197