Trigger to insert TblB from TblA where not exists

[Idokyoku2]

I'm trying to write a trigger to insert TblB from TblA, but
not if that record already exists in TblB. Each table has a different structure, but with some identical columns (those I'm looking to insert) having same datatypes. IDNum is an IDENTITY Column.

It won't fire unless its a straight insert without any conditions, and then obviously insert everything from TblA to TblB regardless.

My pseudo codes:


ALTER TRIGGER TRG_INSERT_TEST
ON TEST
FOR INSERT
AS
SET IDENTITY_INSERT TEST2 ON

INSERT INTO TEST2 (IDNUM, FULLNAME, COMPANY, ADDR2, ADDR1, CITY, STATE, ZIP10)
SELECT DISTINCT X.IDNUM, X.FULLNAME, X.COMPANY, X.ADDR2, X.ADDR1, X.CITY, X.STATE, X.ZIP10
FROM TEST X INNER JOIN TEST2 Y
ON X.IDNUM<>Y.IDNUM
GROUP BY X.FULLNAME, X.COMPANY, X.ADDR2, X.ADDR1, X.CITY, X.STATE, X.ZIP10,
X.IDNUM
HAVING COUNT(*)<2
PRINT ' '
SELECT @@IDENTITY AS 'INSERTED WITH AN IDNUM OF:'

Plese let me know where I've gone wrong.

-David

 

[miq]

HI,

I think this is what you need. I see no need to join tables because TEST is providing all values for TEST2.

ALTER TRIGGER TRG_INSERT_TEST
ON TEST
FOR INSERT
AS
SET IDENTITY_INSERT TEST2 ON

INSERT INTO TEST2 (IDNUM, FULLNAME, COMPANY, ADDR2, ADDR1, CITY, STATE, ZIP10)
SELECT X.IDNUM, X.FULLNAME, X.COMPANY, X.ADDR2, X.ADDR1, X.CITY, X.STATE, X.ZIP10
FROM TEST X 
where NOT Exists(select idnum from TEST2)

PRINT ' '
SELECT @@IDENTITY AS 'INSERTED WITH AN IDNUM OF:'

B.R,
miq

 

[vongrunt]

I'm trying to write a trigger to insert TblB from TblA, but not if that record already exists in TblB.

Assuming that nobody else can insert data into Table2, all you have to check is IDNUM. Otherwise entire approach w/ identities will generate "replication" conflicts. Some other notes:

- @@IDENTITY is potentially dangerous, though this won't show up in this case.
- after no more needed, IDENTITY_INSERT should be turned back OFF
- miq: WHERE clause should be

where IDNUM NOT IN (select IDNUM from TEST2)
-- or --
where NOT EXISTS (select * from TEST2 where TEST2.IDNUM=TEST.IDNUM)

- instead of reference to TEST table, use inserted pseudotable.


------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Idokyoku2]

Hello,
I did finally end up with the later of Vongrunt's suggestions. Identity insert was set to off (a cut and paste issue) but I appreciate the pointer.

Had I not figured this out and neither of you posted, I'd be going mad about now. Thanks for both responses.


Have a good weekend.
-David