Create a Trigger to insert a rows in another table

Question

After create a Stored Procedure in a Table " dbo.terms" to insert a data in it using this code:

CREATE PROCEDURE dbo.terms 
       @Term_en                      NVARCHAR(50)  = NULL   , 
       @Createdate                   DATETIME      = NULL   , 
       @Writer                       NVARCHAR(50)  = NULL   , 
       @Term_Subdomain               NVARCHAR(50)  = NULL  
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.terms
          (                    
            Term_en                     ,
            Createdate                  ,
            Writer                      ,
            Term_Subdomain                 
          ) 
     VALUES 
          ( 
            @Term_en    = 'Cat'               ,
            @Createdate   = '2013-12-12'      ,
            @Writer         = 'Fadi'          ,
            @Term_Subdomain = 'English'                    
          ) 

END 

GO

I want to Create a Trigger in it to add another rows in a table dbo.term_prop

I used this code :

CREATE TRIGGER triggerdata
    AFTER INSERT
    ON dbo.terms
    FOR EACH ROW 
    BEGIN 
      INSERT INTO dbo.term_prop VALUES
      ('قطة', term_ar, upper(:new.term_ar) , null , 'chat', term_fr, upper(:new.term_fr) , null ,'Animal', Def_en, upper(:new.Def_en) , null ,'حيوان', Def_ar, upper(:new.Def_ar) , null ,'Animal', Def_fr, upper(:new.Def_fr) , null); 
    END;

and it shows me an Error

Answer 1

To add more rows you can use SELECTED table.

This is a special table populated with rows inserted in your transaction.

An example is:

  INSERT INTO dbo.term_prop VALUES
  SELECT * FROM inserted

So you mustn't use FOR EACH ROW.

The correct definition of your trigger will be

CREATE TRIGGER triggername ON table AFTER INSERT
AS BEGIN

END

Answer 2

Wow, I am still surprised that triggers are given a bad wrap! I wrote a dozen articles on them a long time ago ...

Like anything in life, the use of triggers depends on the situation.

1 - Trigger are great to track DDL changes. Who changed that table?

http://craftydba.com/?p=2015

2 - Triggers can track DML changes (insert, update, delete). However, on large tables with large transaction numbers, they can slow down processing.

http://craftydba.com/?p=2060

However, with today's hardware, what is slow for me might not be slow for you.

3 - Triggers are great at tracking logins and/or server changes.

http://craftydba.com/?p=1909

So, lets get back to center and talk about your situation.

Why are you trying to make a duplicate entry on just an insert action?

Other options right out of the SQL Server Engine to solve this problem, are:

1 - Move data from table 1 to table 2 via a custom job. "Insert Into table 1 select * from table 2 where etl_flag = 0;". Of course make it transactional and update the flag after the insert is complete. I am just considering inserts w/o deletes or updates.

2 - If you want to track just changes, check out the change data capture. It reads from the transaction log. It is not as instant as a trigger, ie - does not fire for every record. Just runs as a SQL Agent Job in the background to load cdc.tables.

3 - Replicate the data from one server1.database1.table1 to server2.database2.table2.

ETC ...

I hope my post reminds everyone that the situation determines the solution.

Triggers are good in certain situations, otherwise, they would have been removed from the product a long time ago.

And if the situation changes, then the solution might have to change ...

Answer 3

Joe answer is a good one and this is more a advice.

Avoid triggers, they can cause maintenance nightmares: are trick to maintain and debug. If you want to inserts tables in another table after inserting in the first one just put that code in the same SP.

If you need a auto identity generated value you can do it by using @@identity or scope_identity() or ident_current().

Try to keep things simple.