1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
|
CREATE TABLE My
(
Id INT IDENTITY(1,1) CONSTRAINT PKMy PRIMARY KEY
);
CREATE TABLE MyLabel
(
Id INT NOT NULL CONSTRAINT FKMyLabel FOREIGN KEY REFERENCES My(Id),
Label VARCHAR(50) NOT NULL
);
GO
CREATE VIEW VMy
AS
SELECT m.Id, ml.Label
FROM My m
INNER JOIN MyLabel ml
ON ml.Id = m.Id;
GO
CREATE TRIGGER iVMY ON VMy
INSTEAD OF INSERT
AS
BEGIN
DECLARE @are TABLE (
Row bigint IDENTITY(1,1) not null primary key,
Id INT not null
);
MERGE INTO My
USING inserted T
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT
DEFAULT VALUES
OUTPUT inserted.Id INTO @are;
DECLARE @inserted TABLE (
Id INT not null,
Label VARCHAR(55) null
);
INSERT INTO @inserted
SELECT
ISNULL(i.Id, a.Id),
i.Label
FROM (
SELECT
Id,
Label,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS Row
FROM
inserted
) i
LEFT JOIN
@are a
ON a.Row = i.Row;
END;
GO
CREATE TABLE #TEST
(
Label VARCHAR(50)
)
INSERT INTO #TEST
VALUES ('HOP'), ('POUET'), ('TEST')
CREATE TABLE #INSERTED
(
Id INT,
Label VARCHAR(50)
)
INSERT INTO VMy(Label)
OUTPUT inserted.Id, inserted.Label INTO "#INSERTED"
SELECT Label
FROM #TEST
SELECT *
FROM #INSERTED |
Partager