Sql Server trigger insert values from new row into another table

Question

I have a site using the asp.net membership schema. I'd like to set up a trigger on the aspnet_users table that inserted the user_id and the user_name of the new row into another table.

How do I go about getting the values from the last insert?

I can select by the last date_created but that seems smelly. Is there a better way?

Answer 1

You can use OLDand NEW in the trigger to access those values which had changed in that trigger. Mysql Ref

Answer 2

When you are in the context of a trigger you have access to the logical table INSERTED which contains all the rows that have just been inserted to the table. You can build your insert to the other table based on a select from Inserted.

Answer 3

You use an insert trigger - inside the trigger, inserted row items will be exposed as a logical table INSERTED, which has the same column layout as the table the trigger is defined on.

Delete triggers have access to a similar logical table called DELETED.

Update triggers have access to both an INSERTED table that contains the updated values and a DELETED table that contains the values to be updated.

Answer 4

In a SQL Server trigger you have available two psdeuotables called inserted and deleted. These contain the old and new values of the record.

So within the trigger (you can look up the create trigger parts easily) you would do something like this:

Insert table2 (user_id, user_name)
select user_id, user_name from inserted i
left join table2 t on i.user_id = t.userid
where t.user_id is null

When writing triggers remember they act once on the whole batch of information, they do not process row-by-row. So account for multiple row inserts in your code.

Answer 5

try this for sql server

CREATE TRIGGER yourNewTrigger ON yourSourcetable
FOR INSERT
AS

INSERT INTO yourDestinationTable
        (col1, col2    , col3, user_id, user_name)
    SELECT
        'a'  , default , null, user_id, user_name
        FROM inserted

go