39

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
jim
  • 26,598
  • 13
  • 51
  • 66

6 Answers6

70

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
KM.
  • 101,727
  • 34
  • 178
  • 212
  • 3
    Note one difference between what I show and what HLGEM shows is I gave code to show how to handle columns in the new table other than the ones pulled from INSERTED: "a" use a literal constant, "default" use the table defined default, or "null" leave it undefined – KM. Feb 11 '10 at 21:24
  • 1
    Note that if the 2 tables are identical you don't even need to list the column names. Very handy for storing a static read only copy of a table for records that may change later. – Cory House Mar 24 '11 at 01:40
16

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
5

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

Teja Kantamneni
  • 17,402
  • 12
  • 56
  • 86
5

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 2
    Note one difference between what I show and what KM shows is I gave you the code to make sure the record did not already exist in table 2. She/he shows how to handle the other required fields besides the ones you have available in the insert if there are any. – HLGEM Feb 11 '10 at 21:12
4

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.

cmsjr
  • 56,771
  • 11
  • 70
  • 62
0
Create 
trigger `[dbo].[mytrigger]` on `[dbo].[Patients]` after update , insert as
begin
     --Sql logic
     print 'Hello world'     
 end
Nick
  • 138,499
  • 22
  • 57
  • 95
Adil iqbal
  • 9
  • 1
  • 1
    The edited version is wrong. SQL Server doesn’t use back-ticks (that’s MySQL, which, in case it needs to be said is a different DBMS). The square brackets is Microsoft’s own non-standard version. – Manngo Sep 26 '21 at 02:36