语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } } 试试这样 可以吗?
触发器,当主数据库添加一条记录时,临时表EXCHANGE_RATE_INFO就添加一条记录,视图直接查询临时表EXCHANGE_RATE_INFO取得结果。CREATE TRIGGER [COMPANY_EXCHANGE_INFO] ON A.dbo.COMPANY FOR INSERT AS insert into A.dbo.EXCHANGE_RATE_INFO(A, B, C) select d.A,d.B,d.C from Inserted.DATABASE_NM.dbo.XXXX d where d.i_id = '1' 猜的,试试可以吗
我的触发器: CREATE TRIGGER trig_exchange_rate ON MST_COMPANY_INFO FOR INSERT,UPDATE,DELETE AS BEGIN EXEC pro_name END; 这样调用存储过程是可以的,但是pro_name出错了 DB就挂了,没办法rollback啊。
我的触发器: CREATE TRIGGER trig_exchange_rate ON MST_COMPANY_INFO FOR INSERT,UPDATE,DELETE AS BEGIN EXEC pro_name END; 这样调用存储过程是可以的,但是pro_name出错了 DB就挂了,没办法rollback啊。不用存储过程了,直接写sql,向中间表添加数据,一个sql不能写错吧
哦了,问题已经解决。感谢gaijiuyang 和 lanseyoumeng132 我的解决思路是 写了个触发器: CREATE TRIGGER [dbo].[NewTrigger] ON [dbo].[MST_COMPANY_INFO] AFTER INSERT, UPDATE, DELETE AS BEGIN declare @a int, @database_nm varchar(100) set @a = 1 -- 把中间表的数据全部删除 DELETE FROM MST_EXCHANGE_RATE_INFO; -- 查询a库的主表 循环 WHILE @a < (SELECT COUNT(1) FROM MST_COMPANY_INFO WHERE MST_COMPANY_INFO.DATEBASE_NAME <> '') BEGIN -- 获取主表当前循环的库名 SET @database_nm = (SELECT TOP(1) A.DATEBASE_NAME FROM MST_COMPANY_INFO A WHERE A.DATEBASE_NAME <> '' AND A.OFFICE_CODE IN (SELECT TOP(@a) B.OFFICE_CODE FROM MST_COMPANY_INFO B WHERE B.DATEBASE_NAME <> '' ORDER BY B.OFFICE_CODE ASC) ORDER BY A.OFFICE_CODE DESC); -- 循环的参数加1 SET @a = @a + 1; -- 判断@database_nm是哪个库名 然后插入数据 IF @database_nm = 'DATABASE1' BEGIN INSERT INTO MST_EXCHANGE_RATE_INFO ( ORIGINAL_CURRENCY_CODE, TARGET_CURRENCY_CODE, EXCHANGE_RATE, APPLY_START_DATE, APPLY_END_DATE ) SELECT CASE B.cexch_code WHEN 'RMB' THEN 'CNY' ELSE B.cexch_code END AS ORIGINAL_CURRENCY_CODE, CASE C.cexch_code WHEN 'RMB' THEN 'CNY' ELSE C.cexch_code END AS TARGET_CURRENCY_CODE, CASE B.bcal WHEN 1 THEN CAST(A.nflat AS DECIMAL(15,7)) ELSE CAST (1 / A.nflat AS DECIMAL(15,7)) END AS EXCHANGE_RATE, CAST(A.iYear + right('00'+A.iperiod, 2) + '01' as date) AS APPLY_START_DATE, CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,cast(A.iYear + right('00'+A.iperiod, 2) + '01' as date))+1, 0)) AS DATE) AS APPLY_END_DATE FROM [DATABASE1].dbo.exch A LEFT JOIN [DATABASE1].dbo.foreigncurrency B ON A.cexch_name = B.cexch_name AND B.iotherused <> -1 LEFT JOIN [DATABASE1].dbo.foreigncurrency C ON C.iotherused = -1 WHERE A.itype = '2' END .............. END END再写一个视图: 视图里只需要查询[MST_EXCHANGE_RATE_INFO]虚拟表: SELECT DISTINCT * FROM MST_EXCHANGE_RATE_INFO 就可以了。 不多说 散分。
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
} 试试这样 可以吗?
FOR INSERT
AS
insert into A.dbo.EXCHANGE_RATE_INFO(A, B, C)
select d.A,d.B,d.C from Inserted.DATABASE_NM.dbo.XXXX d where d.i_id = '1'
猜的,试试可以吗
CREATE TRIGGER trig_exchange_rate
ON MST_COMPANY_INFO
FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC pro_name
END;
这样调用存储过程是可以的,但是pro_name出错了 DB就挂了,没办法rollback啊。
CREATE TRIGGER trig_exchange_rate
ON MST_COMPANY_INFO
FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC pro_name
END;
这样调用存储过程是可以的,但是pro_name出错了 DB就挂了,没办法rollback啊。不用存储过程了,直接写sql,向中间表添加数据,一个sql不能写错吧
我的解决思路是 写了个触发器:
CREATE TRIGGER [dbo].[NewTrigger]
ON [dbo].[MST_COMPANY_INFO]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
declare @a int, @database_nm varchar(100)
set @a = 1
-- 把中间表的数据全部删除
DELETE FROM MST_EXCHANGE_RATE_INFO;
-- 查询a库的主表 循环
WHILE @a < (SELECT COUNT(1) FROM MST_COMPANY_INFO WHERE MST_COMPANY_INFO.DATEBASE_NAME <> '')
BEGIN
-- 获取主表当前循环的库名
SET @database_nm = (SELECT TOP(1) A.DATEBASE_NAME FROM MST_COMPANY_INFO A WHERE A.DATEBASE_NAME <> '' AND A.OFFICE_CODE
IN (SELECT TOP(@a) B.OFFICE_CODE FROM MST_COMPANY_INFO B WHERE B.DATEBASE_NAME <> '' ORDER BY B.OFFICE_CODE ASC) ORDER BY A.OFFICE_CODE DESC);
-- 循环的参数加1
SET @a = @a + 1;
-- 判断@database_nm是哪个库名 然后插入数据
IF @database_nm = 'DATABASE1'
BEGIN
INSERT INTO MST_EXCHANGE_RATE_INFO
(
ORIGINAL_CURRENCY_CODE,
TARGET_CURRENCY_CODE,
EXCHANGE_RATE,
APPLY_START_DATE,
APPLY_END_DATE
)
SELECT
CASE B.cexch_code WHEN 'RMB' THEN 'CNY' ELSE B.cexch_code END AS ORIGINAL_CURRENCY_CODE,
CASE C.cexch_code WHEN 'RMB' THEN 'CNY' ELSE C.cexch_code END AS TARGET_CURRENCY_CODE,
CASE B.bcal WHEN 1
THEN CAST(A.nflat AS DECIMAL(15,7))
ELSE CAST (1 / A.nflat AS DECIMAL(15,7)) END AS EXCHANGE_RATE,
CAST(A.iYear + right('00'+A.iperiod, 2) + '01' as date) AS APPLY_START_DATE,
CAST(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,cast(A.iYear + right('00'+A.iperiod, 2) + '01' as date))+1, 0)) AS DATE) AS APPLY_END_DATE
FROM
[DATABASE1].dbo.exch A
LEFT JOIN [DATABASE1].dbo.foreigncurrency B ON A.cexch_name = B.cexch_name AND B.iotherused <> -1
LEFT JOIN [DATABASE1].dbo.foreigncurrency C ON C.iotherused = -1
WHERE
A.itype = '2'
END
..............
END
END再写一个视图:
视图里只需要查询[MST_EXCHANGE_RATE_INFO]虚拟表:
SELECT
DISTINCT
*
FROM
MST_EXCHANGE_RATE_INFO
就可以了。
不多说 散分。