sql 触发器的写法

[jerrylan940]

我这里有两张表，如下
table1 Tid ……
table2 Did ……
我需要从table1的Tid自动生成table2的Did，实例如下：
Tid Did
01 0101
01 0102
02 0201
02 0202
03 0301
03 0302

求触发器代码

[csdyyr]

DECLARE @TB TABLE([Tid] VARCHAR(2))
INSERT @TB 
SELECT '01' UNION ALL 
SELECT '01' UNION ALL 
SELECT '02' UNION ALL 
SELECT '02' UNION ALL 
SELECT '03' UNION ALL 
SELECT '03'

SELECT Tid,Did=Tid+RIGHT('00'+RTRIM(SEQ),2)
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY Tid ORDER BY GETDATE()) 
FROM @TB 
) T
/*
Tid  Did
---- ----
01   0101
01   0102
02   0201
02   0202
03   0301
03   0302
*/

[hyde100]

相当于用排名函数了

[JonasFeng]

 
SELECT TID + RIGHT('00' + RID,2) AS DID 
SELECT ROW_NUMBER() OVER(PARTITION BY TID ORDER BY TID) AS RID, * 
FROM TABLE1 T
 

这句话可以生成这DID

但是没明白为什么要用触发器。

[csdyyr]

--2000
DECLARE @TB TABLE([Tid] VARCHAR(2))
INSERT @TB 
SELECT '01' UNION ALL 
SELECT '01' UNION ALL 
SELECT '02' UNION ALL 
SELECT '02' UNION ALL 
SELECT '03' UNION ALL 
SELECT '03'

SELECT *,ID=IDENTITY(int,1,1) INTO # FROM @TB ORDER BY Tid

SELECT Tid,Did=Tid+RIGHT('00'+RTRIM(SEQ),2)
FROM (
SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE Tid<TA.Tid)
FROM # AS TA 
) TB

DROP TABLE #
/*
Tid  Did
---- ----
01   0101
01   0102
02   0201
02   0202
03   0301
03   0302
*/

[kingdomxpxp]

学习了

[SQL77]

DECLARE @TB TABLE([Tid] VARCHAR(2))
INSERT @TB 
SELECT '01' UNION ALL 
SELECT '01' UNION ALL 
SELECT '02' UNION ALL 
SELECT '02' UNION ALL 
SELECT '03' UNION ALL 
SELECT '03'

SELECT DISTINCT (T1.TID+T.TID) TID  FROM @TB T1 CROSS JOIN (SELECT DISTINCT TOP 2 * FROM @TB)AS T

（所影响的行数为 6 行）

TID
----
0101
0102
0201
0202
0301
0302

（所影响的行数为 6 行）