NOT NULL is not a constraint, it is a property of the column definition, the
same as the datatype. INSERT INTO t(a,b) VALUES (1,'aa') will give an error
as well. The INSERT statement still get validated against the columns.
Check constraints, foreign keys etc don't get checked before the trigger
though:
CREATE TABLE t (
a INT PRIMARY KEY,
b CHAR(1) NOT NULL)
GO
CREATE TRIGGER t_tbi ON t INSTEAD OF INSERT AS BEGIN
SET NOCOUNT ON
INSERT INTO t (a,b) (SELECT a,'X' FROM inserted)
END
GO
ALTER TABLE t ADD CONSTRAINT c CHECK ( b='x')
GO
INSERT INTO t(a,b) VALUES (1,'a')
GO
DROP TABLE t
--
Jacco Schalkwijk
SQL Server MVP
[quoted text, click to view] "Stuart McGraw" <smcg4191zz@friizz.RimoovAllZZs.com> wrote in message
news:116g5a5tm7e097@corp.supernews.com...
> Is Microsoft full of #*$#*% (again) or am I badly misunderstanding
> something?
>
> Quote from Microsoft's T-SQL doc:
>> INSTEAD OF triggers are executed instead of the triggering action.
>> These triggers are executed after the inserted and deleted tables
>> reflecting the changes to the base table are created, but before any
>> other actions are taken. They are executed before any constraints,
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> so can perform preprocessing that supplements the constraint actions.
> (SQL Server 2000 sp3a)
>
> CREATE TABLE t (
> a INT PRIMARY KEY,
> b CHAR(1) NOT NULL)
>
> I want to override the value of [b] with the value of 'X' when
> inserting into t...
>
> CREATE TRIGGER t_tbi ON t INSTEAD OF INSERT AS BEGIN
> SET NOCOUNT ON
> INSERT INTO t (a,b) (SELECT a,'X' FROM inserted)
> END
>
> Let's try it...
>
> INSERT INTO t (a,b) VALUES(1,'z')
> SELECT * FROM t
> a | b
> ---|---
> 1 | X
>
> Good, the trigger did what it was supposed to. Lets try a
> slight variation...
>
> INSERT INTO t (a) VALUES(2)
> Server: Msg 233, Level 16, State 2, Line 1
> The column 'b' in table 't' cannot be null.
>
> WTF? What was that I just read about "[instead-of triggers]
> are executed before any constraints"?!?!
>
> What's going on here???
>
