Creating a trigger in ms sql server 2005

Question

SQL

Hi hubr!
Trying to create a trigger in ms sql server 2005

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE Name = 'INSERT_TR_table' AND type = 'TR')
CREATE TRIGGER INSERT_TR_table ON table1
  FOR INSERT
  AS
  DECLARE @temp_row
  DECLARE @pkey int

  select @pkey = ID from inserted;
  SET @temp_row = SELECT top 1 * FROM table1 WHERE ID = @pkey

  IF EXISTS (@temp_row)
      UPDATE table1 SET 
    ID = case(inserted.ID) when '' then @temp_row.ID when null then @temp_row.ID else inserted.ID end,
    field1 = case(inserted.field1) when '' then @temp_row.field1 when null then @temp_row.field1 else inserted.field1 end,
      WHERE ID = @pkey
  ELSE
      INSERT INTO table1 (ID,field1) 
      select * from inserted;
GO

I see a lot of mistakes...

Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'TRIGGER'.
Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '@temp_row'.
Msg 137, Level 15, State 2, Line 49
Must declare the scalar variable "@temp_row".

I know for sure that I need to assign some type to the @temp_row variable, but I don’t know what kind. It should contain the original string. Maybe it's impossible in ms sql at all? I looked all over the search engines today, there is a lot of information, but I did not find what I needed. The meaning of the trigger is simple. If there is a record, it is updated, provided that the new field is not empty, if there is no row, then it is inserted.
In ms sql, I don’t understand almost anything, I looked around msdn, I didn’t find anything worthwhile.
Why trigger? You need to update almost 200k records in 28 tables, plus, there is a possibility of moving to another server, so I create triggers directly in the text of the file with inserts, for each table individually. Perhaps this approach is not correct, if someone tells me a more rational one, I will be grateful. The important point is that you need to work with ms sql server 2005, where there are not many features.

Answer 1

Something like this:

          UPDATE table1 SET  
          field1 = CASE WHEN ISNULL(i.field1,'')='' THEN t.field1 ELSE i.field1 END
          FROM table1 t INNER JOIN inserted i on t.ID=i.ID
          
          INSERT INTO table1 (ID,field1) 
          SELECT ID,field1 FROM inserted i WHERE NOT EXISTS (SELECT TOP 1 ID FROM table1 t WHERE  t.id = i.id)

Answer 2

In a good way @temp_row- not needed inserted- this is a temporary table of inserted records that are placed before calling the trigger code. So treat it like a table (better) or run the cursor over it and do something for each entry (worse).
Well also create triggershould go the first in a pack, such rules in MSSQL. So first check if there is a trigger and delete it, then goand then create the trigger.

Answer 3

Past the branch answered, sorry