insert into table wiht unique index..

[ije]

Hoping someone can assist with this....when a record is created in tableA, i need a corresponding entry made in tableB, if it does not already exist in TableB. TableB has the AccNo field as a , which is the "create unique" index. Is someone able to assist me in creating a trigger that will act as soon as the tableA record is saved, insert it into tableB, if it does not already exist?

Many many thanks in advance,
ije
PS - this is all on MS SQL 2000 servers..

 

[rt63]

create trigger some_trigger on tablea for insert as

insert into tableb
select * from inserted
where not exists
(select accno from tableb join inserted on
tableb.accno = inserted.accno)
RT

 

[ije]

thanks RT,

would this act on just the record being inserted ( desired ), or would it act on any records in tableA not in tableB?

 

[rt63]

It will work on record(s) being inserted and not the entire table.
RT

 

[ije]

Thanks for the help.

When I checked the statement, I recieve an error:

Server: Msg 311, Level 16, State 1, Procedure teamDDE_updateprofile, Line 2
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

If i replace select * from inserted with select * from tableA, it gets around this, but does not help my insert working on the record being added only.

are there limits to what fields you can insert this way?

many many thanks!