instead-of trigger and contraints

[Stuart McGraw]

Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
something?

Quote from Microsoft's T-SQL doc:[color=blue]
> 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,[/color]
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^[color=blue]
> so can perform preprocessing that supplements the constraint actions.[/color]
(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]

Re: instead-of trigger and contraints

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" <smcg4191zz@fri izz.RimoovAllZZ s.com> wrote in message
news:116g5a5tm7 e097@corp.super news.com...[color=blue]
> Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
> something?
>
> Quote from Microsoft's T-SQL doc:[color=green]
>> 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,[/color]
> ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^[color=green]
>> so can perform preprocessing that supplements the constraint actions.[/color]
> (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???
>[/color]

[Jacco Schalkwijk]

Re: instead-of trigger and contraints

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" <smcg4191zz@fri izz.RimoovAllZZ s.com> wrote in message
news:116g5a5tm7 e097@corp.super news.com...[color=blue]
> Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
> something?
>
> Quote from Microsoft's T-SQL doc:[color=green]
>> 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,[/color]
> ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^[color=green]
>> so can perform preprocessing that supplements the constraint actions.[/color]
> (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???
>[/color]

[MGFoster]

Re: instead-of trigger and contraints

Stuart McGraw wrote:[color=blue]
> Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
> something?
>
> Quote from Microsoft's T-SQL doc:
>[color=green]
>>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,[/color]
>
> ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^
>[color=green]
>>so can perform preprocessing that supplements the constraint actions.[/color]
>
> (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???
>[/color]

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQmgjI4echKq OuFEgEQJXQwCfcU 9SQ/m9WrZOEih+5db3s biLg9AAnAky
oxV+MzTp3DTqgwv ySrLyhtRc
=BGR+
-----END PGP SIGNATURE-----

[Martijn Tonies]

Re: instead-of trigger and contraints

[color=blue]
> NOT NULL is not a constraint, it is a property of the column definition,[/color]
the[color=blue]
> same as the datatype.[/color]

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]

Re: instead-of trigger and contraints

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" <jacco.please.r eply@to.newsgro ups.mvps.org.in valid> wrote in message
news:OuKuVwrRFH A.3740@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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" <smcg4191zz@fri izz.RimoovAllZZ s.com> wrote in message
> news:116g5a5tm7 e097@corp.super news.com...[color=green]
> > Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
> > something?
> >
> > Quote from Microsoft's T-SQL doc:[color=darkred]
> >> 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,[/color]
> > ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^[color=darkred]
> >> so can perform preprocessing that supplements the constraint actions.[/color]
> > (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???
> >[/color]
>
>[/color]

[Stuart McGraw]

Re: instead-of trigger and contraints

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" <remove_this_qr an2@yahoo.com> wrote in message news:OWQ$rmrRFH A.3076@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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" <smcg4191zz@fri izz.RimoovAllZZ s.com> wrote in message
> news:116g5a5tm7 e097@corp.super news.com...[color=green]
> > Is Microsoft full of #*$#*% (again) or am I badly misunderstandin g
> > something?
> >
> > Quote from Microsoft's T-SQL doc:[color=darkred]
> >> 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,[/color]
> > ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^[color=darkred]
> >> so can perform preprocessing that supplements the constraint actions.[/color]
> > (SQL Server 2000 sp3a)[/color][/color]
[...snip...]

[--CELKO--]

Re: instead-of trigger and contraints

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]

Re: instead-of trigger and contraints

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--" <jcelko212@eart hlink.net> wrote in message news:1114350282 .340861.155920@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> 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.
>[/color]