Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
You mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Jens,
[vbcol=seagreen]
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:

> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>
|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>