34,575
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tempdb..#Tmp1') IS NOT NULL
DROP TABLE #Tmp1
CREATE TABLE #Tmp1(ID INT PRIMARY KEY,na NVARCHAR(50))
GO
IF OBJECT_ID('tempdb..#Tmp2') IS NOT NULL
DROP TABLE #Tmp2
CREATE TABLE #Tmp2(ID INT,na NVARCHAR(50))
GO
CREATE PROCEDURE #TP (@ID int,@na nvarchar(50))
as
BEGIN TRY
BEGIN TRAN
INSERT INTO #Tmp1
( ID, na )
VALUES ( @ID, -- ID - int
@na -- na - nvarchar(50)
)
COMMIT TRAN
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
BEGIN
ROLLBACK ;
INSERT INTO #Tmp2
( ID, na )
VALUES ( @ID, -- ID - int
@na -- na - nvarchar(50)
)
END
END CATCH
GO
EXEC dbo.#TP @ID =1, -- int
@na = N'a' -- nvarchar(50)
GO 2
SELECT * FROM #Tmp1
SELECT * FROM #Tmp2
ALTER TRIGGER dbo.tri_test
on A
INSTEAD OF INSERT
as
begin
IF(EXISTS(SELECT * FROM A WHERE ID = Inserted.ID))
BEGIN
INSERT INTO B(ID)VALUES(Inserted.Id)
END
ELSE
BEGIN
INSERT INTO A(ID)VALUES(Inserted.Id)
END
END
GO