Hi -

I'm trying to create an insert trigger on a table that will insert an
additional record on the same table.  When an insert occurs on this table
(Trans), I want to check the value of a column ('type') and based on that
value, perform the insert.

Additionaly, I need the value of the identity column (Trans_ID) which will
then be used to populate a column (Trans_ID_O) upon firing of the trigger. 
Here's what I have so far:

CREATE TRIGGER [trGENERATE_NSF_FEE] ON [dbo].[Trans]
FOR INSERT
AS
DECLARE @ORIGINAL_TRAN_ID VARCHAR (15)
--If the newly created record's column 'type' = RCK, move on
IF EXISTS (SELECT * FROM inserted WHERE type = 'RCK' )
BEGIN
--I want to assign @Trans_ID_O as the identity value of the original record
inserted
SET @Trans_ID_O = ( SELECT SCOPE_IDENTITY() From Inserted )

INSERT INTO TRANSACTIONS
(

    Amt ,
    session_ID ,
    [user_name]  ,
    Trans_ID_O,
    )
SELECT

    25  ,
    GETDATE () ,
    @ORIGINAL_TRAN_ID  ,
FROM     TRANSACTIONS
WHERE TYPE = 'RCK'
END

A trigger fires only once if multiple rows are updated. Your trigger
code should look xsomething like this:

CREATE TRIGGER [trGENERATE_NSF_FEE] ON [dbo].[Trans]
FOR INSERT
AS
INSERT INTO TRANSACTIONS
(amt, session_id, trans_id_o)
SELECT 25  , GETDATE (), trans_id
FROM  Inserted
WHERE TYPE = 'RCK'

Still having trouble w/this.  What appears to be happening is that a new
record is inserted for every existing (as opposed to newly inserted).  I'm
also still unable to get the Identity value.   Any help is appreciated.

INSERT INTO TRANSACTIONS
(

    Amt ,
    session_ID ,
    [user_name]  ,
    Trans_ID_O,
    )
SELECT

    TRANS.25  ,
    TRANS.GETDATE () ,
    TRANS.@ORIGINAL_TRAN_ID  ,
FROM     TRANS, INSERTED INSTD
WHERE TYPE = 'RCK'
END

Show quote Hide quote 1.  You joined the tables but failed to qualify how they are joined.  Your
where clause should join TRANS with inserted on the appropriate PK columns.
While you're at it, you might as well get in the habit of using the new join
style (e.g., from TRANS inner join inserted as INSTD on TRANS.xxx =
INSTD.xxx).

2.  It is likely that this code doesn't compile for a number of reasons.
Does the TRANS table have columns name "25", "GETDATE()", and
"@ORIGINAL_TRAN_ID"?  If you want help, it helps (pun intended) to post the
exact code you are using - otherwise we're all wasting time and bandwidth
discussing a fictional situation.

3.  The TRANSACTIONS.[user_name] column contains some sort of ID value?  Are
you sure this is correct? If so, then you should use the column in the
inserted table that corresponds to the value you want to use - it appears
that this should be the identity column of the TRANS table (with the
appropriate conversion - code defensively and do the actual cast/convert
yourself rather than relying on implicit conversion).

4.  You don't need to join the inserted table with anything else in this
situation.  The rows in the inserted table are the rows that were inserted
by the trigger statement and contain all of the information you need.  David
posted the exact statement (as far as I can tell) that you need - is there a
reason you chose to not use that statement?

In a FOR or AFTER INSERT trigger, the IDENTITY column of the inserted
pseudotable contains the valid IDENTITY values for each inserted row.  Also,
you should be testing TRIGGER_NESTLEVEL(OBJECT_ID(N'triggerName') to prevent
recursive execution of the trigger, for example:
CREATE TRIGGER triggerName ON TRANSACTIONS FOR INSERT AS
BEGIN
    IF TRIGGER_NESTLEVEL(OBJECT_ID(N'triggerName')) = 1
    BEGIN
        INSERT INTO TRANSACTIONS
         (
         Amt ,
         session_ID ,
         [user_name]  ,
         Trans_ID_O
         )
         SELECT  25  ,
             GETDATE () ,
             [user_name],
             Trans_ID
         FROM inserted
        WHERE TYPE = 'RCK'
    END
END

Show quoteHide quote

Thanks for everyone's input -  I'm on the right track.

SQL HELP
INSTEAD OF INSERT TRIGGER AND NULL
obtaining the ip address of connection
Use a stored procedure from a function
triggers