有以下两个表
XXX_Master
XXX_Detail
两个表都有 no,flag这个两个字段
no是两个表主从表的关联值
当XXX_Master的flag字段值被改动时,XXX_Detail的flag的字段值都相应改动(no字段是主从表关联字段)现在想给数据库中所有的表实现: 所有表名称以_Master结尾的的表增加一个这样的触发器(如果用触发器不合理,还可以用什么方法实现类似的功能)
应该怎么做?
XXX_Master
XXX_Detail
两个表都有 no,flag这个两个字段
no是两个表主从表的关联值
当XXX_Master的flag字段值被改动时,XXX_Detail的flag的字段值都相应改动(no字段是主从表关联字段)现在想给数据库中所有的表实现: 所有表名称以_Master结尾的的表增加一个这样的触发器(如果用触发器不合理,还可以用什么方法实现类似的功能)
应该怎么做?
Create Procedure usp_CreateTrigger
As
Begin
Declare @Tbl Table (TblName Varchar(100))
--获取表命
Insert Into @Tbl
Select SUBSTRING(Name, 1, CHARINDEX('_', Name) - 1) From Sysobjects Where Name Like '%_Master' Declare @tName varchar(100), @TB_Master varchar(128), @TB_Detail Varchar(128), @SQL Varchar(8000)
Declare tItem cursor for
SELECT TblName From @Tbl
Open tItem
FETCH NEXT FROM tItem into @tName
WHILE (@@FETCH_STATUS <> -1) and (@@FETCH_STATUS <> -2)
BEGIN
Set @TB_Master = @tName + '_Master'
Set @TB_Detail = @tName + '_Detail'
Set @SQL = 'Create Trigger tru_'+@TB_Master+'_Flag
On '+@TB_Master+'
for Update
As
if Update(Flag)
begin
Update A
Set A.Flag = B.Flag
From '+@TB_Detail+' A ,Inserted B
Where A.NO=B.No
end '
Print @SQL
Exec(@SQL)
FETCH NEXT FROM tItem into @tName
END
CLOSE tItem
DEALLOCATE tItem
End
当XXX_Master的flag字段值被改动时,XXX_Detail的flag的字段值都相应改动LZ 这个不是主从关系? 通过no就可以直接标识了。不需要同步更新吧?
select B.name,A.flag --此处A的flag可以用判断处理,比如B中flag跟A中的flag冲突的时候,取B的flag等等
from B
inner join A on A.id = B.a_id参考.
USE TEMPDB
GO
IF OBJECT_ID('A_MASTER') IS NOT NULL DROP TABLE A_MASTER
IF OBJECT_ID('A_DETAIL') IS NOT NULL DROP TABLE A_DETAIL
IF OBJECT_ID('B_MASTER') IS NOT NULL DROP TABLE B_MASTER
IF OBJECT_ID('B_DETAIL') IS NOT NULL DROP TABLE B_DETAIL
GO
CREATE TABLE A_MASTER(NO INT,FLAG INT)
CREATE TABLE A_DETAIL(NO INT,FLAG INT)
CREATE TABLE B_MASTER(NO INT,FLAG INT)
CREATE TABLE B_DETAIL(NO INT,FLAG INT)
GO
INSERT INTO A_MASTER
SELECT 1,1
GO
INSERT INTO A_DETAIL
SELECT 1,1
GO
INSERT INTO B_MASTER
SELECT 1,1
GO
INSERT INTO B_DETAIL
SELECT 1,1
GO
SELECT 'CREATE TRIGGER TRI_'+NAME+' ON '+NAME+'
FOR UPDATE
as
begin
UPDATE T1 SET T1.FLAG=T2.FLAG
FROM '+NAME+' T1
INNER JOIN '+LEFT(TB.NAME,CHARINDEX('_',TB.NAME)-1)+'_DETAIL T2 ON T1.NO=T2.NO
INNER JOIN INSERTED T3 ON T1.NO=T3.NO
end'
FROM SYS.TABLES TB
WHERE NAME LIKE '%_[_]MASTER'
AND EXISTS(
SELECT 1 FROM SYS.TABLES TB2 WHERE TB2.NAME LIKE '%_[_]DETAIL'
AND LEFT(TB2.NAME,CHARINDEX('_',TB2.NAME)-1)=LEFT(TB.NAME,CHARINDEX('_',TB.NAME)-1)
)