More efficient way of checking for value

This trigger needs to check the contact1 table for the deleted accountno. If
it is not found there it will then use the record in contact1del.
This trigger works but I know there is a better way to check for the deleted
accountno in contact1 in the IF statement. Accountno values in Contact1 are
always unique
Thank you in advance for reading this. Any help and\or advise on this
trigger in general is greatly appreciated
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from Contact1
)
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join
contact1
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
END
ELSE
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
FROM deleted
INNER join
contact1del
ON contact1del.accountno = deleted.accountno
WHERE contact1del.rectype IS NULL and deleted.rectype='C'
ENDJenks,
Try using EXISTS instead.
See:
http://msdn.microsoft.com/library/d...br />
0a2b.asp
HTH
Jerry
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:1D3AE7DF-B678-461B-B0FF-11F4010DFDD6@.microsoft.com...
> This trigger needs to check the contact1 table for the deleted accountno.
> If
> it is not found there it will then use the record in contact1del.
> This trigger works but I know there is a better way to check for the
> deleted
> accountno in contact1 in the IF statement. Accountno values in Contact1
> are
> always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
>|||jenks wrote:
> This trigger needs to check the contact1 table for the deleted
> accountno. If it is not found there it will then use the record in
> contact1del.
> This trigger works but I know there is a better way to check for the
> deleted accountno in contact1 in the IF statement. Accountno values
> in Contact1 are always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )
But your original code does not handle multi-row deletes where one row
deleted exists in the Contact1 table and one row that is deleted does
not. Tthe EXISTS code above won't work correctly in that case either.
You could just always run both inserts for rows that exist and for rows
that do not, assuming those rows are mutually exclusive and forget
checking for existence.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In this case, the deleted rows with rectype of 'C' will always have an
accountno value in one of the two tables(contact1 or contact1del). EXISTS
will always work, unless I am missing something.
Thanks alot guys. Really appreciate it.
"David Gugick" wrote:
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )

> But your original code does not handle multi-row deletes where one row
> deleted exists in the Contact1 table and one row that is deleted does
> not. Tthe EXISTS code above won't work correctly in that case either.
> You could just always run both inserts for rows that exist and for rows
> that do not, assuming those rows are mutually exclusive and forget
> checking for existence.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||David, I just read your response more carefully and I see I missed your poin
t.
In this case, the records are touched through a front end app. All Contsupp
records with a rectype of 'C' will always have an associated Accountno in
Contact1. It is possible to delete individual Contsupp records, in which cas
e
the first part of the query with the IF EXISTS statement will be true. The
second part of the trigger is to handle deletion of Contact1 records. When a
Contact1 record is deleted, the associated Contsupp records are automaticall
y
deleted imediately after the Contact1 record. (there is a deletion trigger o
n
Contact1 as well).
Again, thank you for taking a look
"jenks" wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del). EXISTS
> will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
>|||jenks wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del).
> EXISTS will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
Let's say that two rows are deleted in a single statement:
Delete from dbo.Contsupp
Where accountno in (1, 2)
One of the rows deleted exists in Contact1. The other one doesn't. The
EXISTS statement will return true because one row exists in the
relationship between deleted and Contact1 and the corresponding insert
will take place. However, there is another row in the deleted table
which does not exist in Contact1 and its insert will not be executed.
So I think you still have a problem in your code.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. Is
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, Pancho
You can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho
|||Hi
There is a script on
http://www.sqlservercentral.com/Scri...0&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:

> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily. Is
> there an automated way or Administrative Tool to monitor the decline of free
> disk space on 4 logical drives? Thanks, Pancho
|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...[vbcol=seagreen]
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scri...0&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. I
s
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, PanchoYou can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho|||Hi
There is a script on
http://www.sqlservercentral.com/Scr...20&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:

> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from m
y
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
Is
> there an automated way or Administrative Tool to monitor the decline of fr
ee
> disk space on 4 logical drives? Thanks, Pancho|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...[vbcol=seagreen]
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scr...20&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:
>

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. Is
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, PanchoYou can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho|||Hi
There is a script on
http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Maintenance%20and%20Management%20&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily. Is
> there an automated way or Administrative Tool to monitor the decline of free
> disk space on 4 logical drives? Thanks, Pancho|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scripts/listscripts.asp?categorynm=Maintenance%20and%20Management%20&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:
>> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
>> manually checking it each day and comparing growth of the .bak file from
>> my
>> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
>> Also, I have 4 other drives with non-DB image files that are added daily.
>> Is
>> there an automated way or Administrative Tool to monitor the decline of
>> free
>> disk space on 4 logical drives? Thanks, Pancho