By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,932 Members | 1,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,932 IT Pros & Developers. It's quick & easy.

instead-of trigger and contraints

P: n/a
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???

Jul 23 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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???

Jul 23 '05 #2

P: n/a
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???

Jul 23 '05 #3

P: n/a
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-----
Jul 23 '05 #4

P: n/a
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
Jul 23 '05 #5

P: n/a
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???



Jul 23 '05 #6

P: n/a
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...]
Jul 23 '05 #7

P: n/a
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.

Jul 23 '05 #8

P: n/a
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.

Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.