Better way to check for value

Question

Post reply

Better way to check for value

John Jenkins

SSC Veteran

Points: 200
More actions
October 10, 2005 at 3:54 pm

#89470

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(NOLOCK)
    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(NOLOCK)
    ON contact1del.accountno = deleted.accountno
WHERE contact1del.rectype IS NULL and deleted.rectype='C'
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

The trigger works?

This Looks a little funky

if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from Contact1)

try

if exists (select * from Contact1 join deleted on Contact1.accountno = deleted.accountno)

Begin

......

end

else

begin

......

end

John Jenkins SSC Veteran Points: 200 More actions · Answer 2

Yeah it worked. Definitely funky though

EXISTS is the way to go

Thanks for the reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

1)Beware that triggers are set-based

2)Only modified the second insert to include a check on contact1

CREATE TRIGGER Supp_contact_delete ON Contsupp

FOR DELETE

AS

SET NOCOUNT ON /*avoiding roundtrip, trigger doesn't return records*/

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(NOLOCK) /*only the ones in contact1*/

ON contact1.accountno = deleted.accountno

WHERE deleted.rectype='C' AND deleted.accountno > ''

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(NOLOCK) ON contact1del.accountno = deleted.accountno

left join contact1(NOLOCK) /*added,only the ones not in contact1*/

ON contact1.accountno = deleted.accountno

WHERE contact1del.rectype IS NULL and deleted.rectype='C'

AND contact1.accountno IS NULL /*not in Contact1*/

John Jenkins SSC Veteran Points: 200 More actions · Answer 4

Because of the nature of the application, there will never be a batch of deleted Contsupp rows that have a link in Contact1 AND Contact1del. It is not possible to create that scenario through the front end and would take some mangling by some one in the backend to create the scenario.

For that reason I think my trigger would work fine.

Jo, yours looks cleaner though and seems to work for me. I want to make sure I understand what it is doing though. Does it attempt to fire both sections every time ?

thank you

John

noeld SSC Guru Points: 96590 More actions · Answer 5

Correct! Jo's will execute both but because they are mutually exclusive only one of them will contain rows.

As a bit of optimization you can try a "union all" instead

insert ...

select -- /*only the ones in contact1*/

union all

select -- /*added,only the ones not in contact1*/

* Noel