instead-of trigger and contraints

[Stuart McGraw]

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???

[Quentin Ran]

Stuart,

I am not sure you got the right document. I can not locate your quote in
BOL, and the quote "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." is clearly not correct. MS is not that
stupid.

Can you provide the way you found the quote?

Quentin

"Stuart McGraw" <sm********@friizz.RimoovAllZZs.com> wrote in message
news:11************@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???

[Jacco Schalkwijk]

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
"Stuart McGraw" <sm********@friizz.RimoovAllZZs.com> wrote in message
news:11************@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???

[MGFoster]

Stuart McGraw wrote:

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???

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Constraints are:

CREATE TABLE t (a char(1) CHECK (a <> 'z'))

The CHECK is the constraint that makes sure the column [a] cannot have
the value 'z' stored in it.

Other CONSTRAINTS are Primary Key, Foreign Key and Default.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgjI4echKqOuFEgEQJXQwCfcU9SQ/m9WrZOEih+5db3sbiLg9AAnAky
oxV+MzTp3DTqgwvySrLyhtRc
=BGR+
-----END PGP SIGNATURE-----

[Martijn Tonies]

NOT NULL is not a constraint, it is a property of the column definition, the same as the datatype.

Ehm, actually, "not null" IS a constraint. Apparently, it's not in MS SQL
speak, but it is a constraint, a "column level constraint" to be exact.

:-)
--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

[Stuart McGraw]

Sigh. Now that you mention it I have a vague recollection of
encountering this semantic difference in the past. I have been
working with Postgresql a lot lately so the distinction did not
occur to me. Thanks (even though it was not what I wanted
to hear!)

"Jacco Schalkwijk" <ja****************@to.newsgroups.mvps.org.invalid > wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...

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
"Stuart McGraw" <sm********@friizz.RimoovAllZZs.com> wrote in message
news:11************@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???

[Stuart McGraw]

SQL Books Online
- Creating and Maintaining Databases
- - Enforcing Business Rules with Triggers
- - - Designing Triggers
- - - - Trigger Execution
- - - - - (2nd paragraph)

maybe you were thinking of "after" triggers?

"Quentin Ran" <re***************@yahoo.com> wrote in message news:OW**************@TK2MSFTNGP14.phx.gbl...

Stuart,

I am not sure you got the right document. I can not locate your quote in
BOL, and the quote "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." is clearly not correct. MS is not that
stupid.

Can you provide the way you found the quote?

Quentin

"Stuart McGraw" <sm********@friizz.RimoovAllZZs.com> wrote in message
news:11************@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)

[...snip...]

[--CELKO--]

In Standard SQL, constraints can be declared so that you can defer
them until the end of the session and temporarily have an illegal state
in a block of code. The rule in SQL is that all constraints must be
enforces at the end of a session. So you override the rules until you
leave the unit of work and all of them are checked. It is executed
BEFORE constraints, not INSTEAD OF constraints.

[Stuart McGraw]

That would be the DEFFERED keyword I guess, which I see in Oracle and
in Postgresql (for foreign key contraints only) but not in SQL Server 2k.

"--CELKO--" <jc*******@earthlink.net> wrote in message news:11**********************@g14g2000cwa.googlegr oups.com...

In Standard SQL, constraints can be declared so that you can defer
them until the end of the session and temporarily have an illegal state
in a block of code. The rule in SQL is that all constraints must be
enforces at the end of a session. So you override the rules until you
leave the unit of work and all of them are checked. It is executed
BEFORE constraints, not INSTEAD OF constraints.