CODEWITHSUNDEEP
sqlsql-servertriggers
jim
jim

Reputation: 27396

Sql Server trigger insert values from new row into another table

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?

Upvotes: 40

Views: 193215

Answers (6)

Adil iqbal
Adil iqbal

Reputation: 9

Create 
trigger `[dbo].[mytrigger]` on `[dbo].[Patients]` after update , insert as
begin
     --Sql logic
     print 'Hello world'     
 end

Upvotes: 0

KM.
KM.

Reputation: 103589

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

Upvotes: 72

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 5

Oded
Oded

Reputation: 498972

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.

Upvotes: 16

cmsjr
cmsjr

Reputation: 59175

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.

Upvotes: 4

Teja Kantamneni
Teja Kantamneni

Reputation: 17472

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

Upvotes: 5

Related Questions