34,588
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER dbo.trig_A_I
ON dbo.A
AFTER INSERT
AS
BEGIN
......
END
USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('Proc_Exec_A_B') IS NOT NULL DROP PROC Proc_Exec_A_B
GO
CREATE TABLE A(
idA INT IDENTITY(1,1) PRIMARY KEY,
B INT
)
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-03-17
-- Description:
-- =============================================
CREATE PROC Proc_Exec_A_B
@B INT
AS
BEGIN
SET NOCOUNT ON
PRINT '刚才插入记录的B字段的值为:'+str(@B)
END
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-03-17
-- Description: A表触发器,插入时执行存储过程
-- =============================================
CREATE TRIGGER dbo.trig_A_I
ON dbo.A
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t TABLE (
id INT IDENTITY(1,1),
B INT
)
INSERT INTO @t(B)
SELECT t.B
FROM INSERTED AS t
DECLARE @i INT,@imax INT,@B INT
SELECT @i=1,@imax=MAX(id) FROM @t
WHILE @i<=@imax
BEGIN
SELECT @B=B FROM @t WHERE id=@i
EXEC dbo.Proc_Exec_A_B @B
SET @i=@i+1
END
END
GO
---------------- 验证 -------------
SET NOCOUNT ON
INSERT INTO A(B)
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
/*
刚才插入记录的B字段的值为: 3
刚才插入记录的B字段的值为: 2
刚才插入记录的B字段的值为: 1
*/