When
running the code below on SQL Server 2008 and above, what will the
final query (with the comment "Predict the output of this
query") return?
CREATE
TABLE
dbo.PredictOutput
(in_pk
int NOT
NULL,
in_pk_value
int NOT
NULL,
PRIMARY
KEY (in_pk)
);
go
CREATE
TRIGGER
TestTrig
ON
dbo.PredictOutput
FOR
INSERT
AS
IF
(SELECT
COUNT(*)
FROM
INSERTED
WHERE
in_pk_value
< 0)
= 1
BEGIN;
RAISERROR
('Negative
values are not allowed!',
16, 1);
ROLLBACK
TRAN;
END;
go
--
Insert attempt #1
INSERT
INTO
dbo.PredictOutput
(in_pk,
in_pk_value)
VALUES
(1,
1);
go
--
Insert attempt #2
INSERT
INTO
dbo.PredictOutput
(in_pk,
in_pk_value)
VALUES
(2,
-2);
go
--
Insert attempt #3
INSERT
INTO
dbo.PredictOutput
(in_pk,
in_pk_value)
VALUES
(3,
-3),
(4,
-4);
go
--
Predict the output of this query:
SELECT
COUNT(*)
FROM
dbo.PredictOutput;
go
DROP
TRIGGER
TestTrig;
go
DROP
TABLE
dbo.PredictOutput;
go
Answer:
3
Explanation:
An insert trigger fires once per execution of a statement that may
cause rows to be inserted. During that single execution, all inserted
rows will be in the INSERTED pseudo-table (also called magic table).
The trigger here works correctly only if a single row is
inserted.
Coding
triggers that works correctly only if a single row is inserted is bad
practice. Even if the application currently inserts rows one at a
time, it may change in the future.
In
the case of this question, insert attempt #1 succeeds (as there are
no rows inserted with in_pk_value below zero).
Insert
attempt #2 fails (the negative value in in_pk_value causes an error
message and a rollback). This aborts the batch, but the next batch
(starting at the next "go") will execute normally.
Insert
attempt #3 then succeeds. Both inserted rows violate the business
rule, but the trigger here only causes an error message if the
number of violations is exactly one.
The
correct way to code this would have been to use IF EXISTS (SELECT *
FROM INSERTED WHERE in_pk_value < 0). Incidentally, this would
also have been more efficient.
No comments:
Post a Comment