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

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data fro
m
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 wan
t
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
ENDYou 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 f
rom
> 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 w
ant
> 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. Referentia
l
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,

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:
[vbcol=seagreen]
> 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
> --
>
>

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
ENDYou 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,
> > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
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 => > 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
> >
> >
>
>|||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
> --
>
>

About trigger

is it possible to run a trigger forever... whose work is to archive data from a table. or how can i do the operation to archive automatically ?

Regards,

Kamrul Hassan

Well... triggers cannot be used for schedule tasking, there are certain some ways to do this.
1st) if you are running on dedicated server then you can use windows service to update records periodically
2nd) Use Sql Server jobs. to know more about sql server jobs http://msdn2.microsoft.com/en-us/library/ms187880.aspx

cheers..

about Store Procedure

Hi i have store procedure called runontime suppose... i want to run that
store procedure mid night from monday to friday automatically . is their any
facility available in sql server 2005 to do that like timer thanksCreate job? Have you check it out?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||Use the SQL Server agent - that's what it's there for! :)
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||hi can u direct me to any artical or help about it thanks
"Uri Dimant" wrote:

> Create job? Have you check it out?
>
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
>
>|||Ok, open BOL and put it in URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/079c2984-0052-4a37-a2b8-4ece
56e6b6b5.htm
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:CF8B8E49-E0E0-4585-BD03-72D423092AA1@.microsoft.com...
> hi can u direct me to any artical or help about it thanks
> "Uri Dimant" wrote:
>