1. 如何查看本地数据库已经创建了多少触发器2. 触发器这样写可以么? 不用distribution , 我这 DTC 有问题CREATE TRIGGER tr_in_tt on [Northwind].[dbo].[t]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @id INT, @name VARCHAR(20);
SELECT @id=id, @name=name FROM inserted;
INSERT INTO TestLocal.pubs.dbo.test(id,name) VALUES(@id,@name);
end但是执行失败 , 提示 Northwind.dbo.t as the target 不在当前数据库,
可是当前本地数据库是有这个表的t .
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @id INT, @name VARCHAR(20);
SELECT @id=id, @name=name FROM inserted;
INSERT INTO TestLocal.pubs.dbo.test(id,name) VALUES(@id,@name);
end但是执行失败 , 提示 Northwind.dbo.t as the target 不在当前数据库,
可是当前本地数据库是有这个表的t .
select * from sysobjects where xtype='TR'
create table t(id int,[name] varchar(10))
create table test(id int,[name] varchar(10))
go
CREATE TRIGGER tr_in_tt on [dbo].[t]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @id INT, @name VARCHAR(20);
SELECT @id=id, @name=name FROM inserted;
INSERT INTO dbo.test(id,name) VALUES(@id,@name);
end
go
insert into t select 1,'abc'
go
select * from test
go
drop table t,test
/*
id name
----------- ----------
1 abc(1 行受影响)*/
====
需要是当前DB
-- SQL Server 2005
select * from sys.triggers;
-- SQL Server 2000
select * from sysobjects where type='TR';2、on [Northwind].[dbo].[t] 子句中指定的表名只可以为两部分名。也就是说,只能在表所在的数据库上创建该表的触发器,不能在数据库 A 上创建数据库 B 中表的触发器。
select northwind
go