Newbie Trigger how should I do it?

for a long list of samples etc, see BOL, or Books Online (comes with the sql server installation, and can also be found online on msdn).

in brief, there are 3 types of triggers, that can be triggered by DML statements Insert, Update and Delete, or any combination in between.

The 3 types are:
FOR - this is standard trigger, and executes within the same transaction as the DML call
AFTER - this is code executed after the DML action is done
INSTEAD OF - this replaces the actual DML with your code. Typically used on views, which displays data from 2 or more tables, and updates the required base tables

you can have multiple triggers on the same object (table or view), and have (limited) control of execution order only by explicitly changing it.

The defining factor of all triggers are to special table, Inserted and Deleted. They are only accessible within triggers.

the concept is simple: the Inserted holds all rows that are new, the Deleted rows all rows that are old.

For an Insert Trigger:
the Inserted table will hold all rows about to be inserted
the Deleted table will be empty

For a Delete Trigger:
the Inserted table will be empty
the Deleted table will hold all rows about to be deleted

For an Update Trigger:
the Inserted table will hold New version of rows about to be updated
the Deleted table will hold old version of rows about to be updated

your pseudo code could be translated something in the line of:

CREATE trigger distribute_import_data on impTable
for insert
as
begin

--first the update, to only catch existing records
update target1Table
set target1Table.col1 = i.col1,
target1Table.col2 = i.col2
from target1Table inner join inserted i on target1Table.adrid = i.adrid

--now, insert any new rows added
insert into target1Table (adrid, col1, col2)
select i.adrid, i.col1, i.col2 from Inserted i
where i.adrid not in (select t.adrid from target1Table t)
end

HTH

Fridthjof

Thanx a million for your answers.

I am getting a hang of it now I think. But still some problems. And it is the error handling. This is an example in my stored procedure, cause i suppose it works the same in my trigger.

The problem is here that I cannot catch for instance the error

Server: Msg 547, Level 16, State 1, Procedure distribute_plandata, Line 26
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Planering_Adress'. The conflict occurred in database 'myWSPDB2', table 'Adress', column 'adress_inc'.
The statement has been terminated.

with the @@error variable. Shouldn't this kind of message get trapped and jump down to my logError code segment?

My stored procedure:

CREATE PROCEDURE dbo.SPInsertIntoRSImport

(
@adrID integer,
@ture integer,
@seq integer,
@arrivalTime float,
@losdate smalldatetime,
@startdate smalldatetime,
@losname char(30),
@user char(10),
@stopdate smalldatetime,
@planid integer
)

AS BEGIN
/* SET NOCOUNT ON */

declare @Error int

begin transaction
insert into rsimport(Adressid, ture, seq, arrival_time, soldate, startdate, solname, user) values (1231, @ture, @seq, @arrivalTime, @losdate, @startdate, @losname, @user)

set @Error = @@ERROR
if @Error <> 0 --if error is raised
begin
goto LogError
end
commit transaction
goto ProcEnd

LogError:
declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
insert into error_log (logdate,source,errmessage)
values (getdate(),'SPInsertIntoRSImport',@ErrMsg)

ProcEnd:
end

RETURN
GO