partitoned view not firing trigger on the table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

partitoned view not firing trigger on the table

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2005-06-08 : 14:00:40

Hi,

We have a partition view that is based on couple of tables partitioned by date range.We have a trigger on the tables. When we insert into the view sql server looks into the check constraint and insert into the correct table but did not fire the trigger (if the table is in other database than the view) But works if table is in the same database so basically problem is SQL Server is not firing triggers in other database if we insert into a view that is based on table of other database.
Here is an example:
create table dd (dd int not null, dd1 int not null,
constraint ck_dd check(dd1 between 1 and 5),constraint pk_dd primary key(dd,dd1))
create table db2.dbo.dd1 (dd int not null, dd1 int not null,
constraint ck_dd1 check(dd1 between 6 and 10),constraint pk_dd1 primary key(dd,dd1))
create table dd2 (dd int not null, dd1 int not null)
create table db2.dbo.dd3 (dd int not null, dd1 int not null)

create view vw_dd as
select * from dbo.dd
union all
select * from db2.dbo.dd1

create trigger trig_dd
on dd
for insert
as
insert into dd2 select * from inserted

create trigger db2.dbo.trig_dd1
on db2.dbo.dd1
for insert
as
insert into db2.dbo.dd3 select * from inserted

This statement fire triggers since view is in same database as table dd:
insert into vw_dd values(1,1)

This statement don’t fire triggers since view is in database db1 whereas table dd1 is in database db2

insert into vw_dd values(1,8)

Thanks
--rubs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources