INSERT TRIGGER 1

[stucoo]

Hi,

I have an application that inserts data via an ODBC connection into an SQL 2000 database. I'm trying to create a trigger that will check that the data being inserted does not already exist in the table. If it does exist I want the entry to be updated with the new data, otherwise the data needs to be inserted as normal.

Has anyone any suggestions?

Many Thanks

 

[maswien]

If I were you, I would use a SP rather than a trigger, then call this SP from your application,

CREATE PROCEDURE theSP
@theKey ...,
@otherColumn ...
AS
update myTable set otherColumn = @otherColumn
where theKey=@theKey

if (@@rowcount = 0)
begin
insert into myTable values(@theKey, @otherColumn)
end

 

[stucoo]

I agree. Unfortunately I would have to get the application re-written, so that option is not available.

 

[dbomrrsm]

I would bring the new data into a temp table and then do an update on the existing table from those that exist in the temp table and an insert into existing table where they dont already exist.

[blue]DBomrrsm[/blue]