考虑以下最小、完整且可验证的示例代码(请参阅此处的 dbfiddle):
CREATE TABLE [dbo].[test]
(
[i] bigint NOT NULL
identity(1,1)
PRIMARY KEY CLUSTERED
, [d] varchar(10) NOT NULL
);
GO
使用INSTEAD OF INSERT, UPDATE
触发器:
CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
IF ROWCOUNT_BIG() = 0 RETURN;
SET NOCOUNT ON;
MERGE INTO [dbo].[test] [target]
USING [inserted] [source] ON [target].[i] = [source].[i]
WHEN NOT MATCHED THEN
INSERT
(
[d]
)
VALUES
(
[source].[d]
)
WHEN MATCHED THEN
UPDATE
SET [target].[d] = [source].[d];
END;
GO
我正在对表进行插入,希望获得插入的标识值,但是返回的值是0
:
DECLARE @output TABLE
(
[i] bigint NOT NULL
, [d] varchar(10) NOT NULL
);
INSERT INTO [dbo].[test]
(
[d]
)
OUTPUT
[inserted].[i]
, [inserted].[d]
INTO @output
(
[i]
, [d]
)
VALUES ('test');
/* shows [i] is 0 */
SELECT *
FROM @output;
/* shows [i] is 1 */
SELECT *
FROM [dbo].[test];
结果是:
我 | d |
---|---|
0 | 测试 |
和
我 | d |
---|---|
1 | 测试 |
期望的结果是两组输出匹配,但事实并非如此。
我究竟做错了什么?
我已经看到了这一点,但是这似乎很不同,因为我根本没有使用视图。在我的示例中,触发器位于桌子上。