SQL Trigger use inserted values to call stored procedure

Question

I'm trying to do the following but struggling to get the syntax correct to pass the inserted values to my stored procedure.

CREATE TRIGGER tInsertCall 
   ON cdr2
   FOR INSERT
AS 
BEGIN
    EXEC Track.dbo.InsertCall inserted.calldate, inserted.clid, 
               inserted.src, inserted.dst, inserted.dcontext, inserted.channel, 
               inserted.dstchannel, inserted.lastapp, inserted.lastdata, 
               inserted.duration, inserted.billsec, inserted.disposition, 
               inserted.uniqueid, inserted.amaflags, inserted.recordingpath,
               inserted.accountcode, inserted.userfield
END
GO

Thanks

Inthe first place, never call a stored provc from a trigger. What happens when multiple values are inserted in one statement? Thie trigger must handel set-based processes and NEVER UNDER ANY CIRCUMSTANCES run one record at a time. — HLGEM
– HLGEM, Commented Sep 8, 2014 at 20:09
It would be helpful to also post what error you get, I suspect you are attempting to use the inserted table (which is a table not a single row) as a single row. — Andrew
– Andrew, Commented Sep 8, 2014 at 20:09
The cdr2 table only ever has a single row inserted per statement. — Ben Durkin
– Ben Durkin, Commented Sep 8, 2014 at 20:13
You can't call a stored procedure using the columns from a table directly. If you are absolutely certain that you always insert only one row in that table per transaction, then you could first assign the columns of that row to different variables and then run the sp with those variables. Even so, you should still not create a trigger that can't handle more than 1 row per transaction — Lamak
– Lamak, Commented Sep 8, 2014 at 20:16

Hannah Vernon · Accepted Answer · 2014-09-08 21:04:00Z

You could create something like this:

CREATE TRIGGER dbo.tInsertCall ON dbo.cdr2
   FOR INSERT
AS 
BEGIN

    DECLARE @CallDate DATETIME;
    DECLARE @clid INT;
    .    --Add the other variables here
    .
    .
    DECLARE @userfield SOMEDATATYPE;

    SELECT TOP(1) @CallDate = INSERTED.calldate
        , @clid = INSERTED.clid
        ...    --Add the other variables here
        , @userfield = INSERTED.userfield
    FROM INSERTED;

    EXEC Track.dbo.InsertCall @CallDate, @clid, ... /*other variables here*/, @userfield;
END
GO

This trigger will only process the 1st row from the INSERTED virtual table, since it has TOP(1) in the SELECT statement.

The better (arguable) way to do this would be to use a trigger to iterate over all the rows in INSERTED. Something like:

CREATE TRIGGER dbo.tInsertCall ON dbo.cdr2
   FOR INSERT
AS 
BEGIN

    DECLARE @CallDate DATETIME;
    DECLARE @clid INT;
    .   -- add the other variable declarations here
    .
    .
    DECLARE @userfield SOMEDATATYPE;

    -- use FAST_FORWARD and LOCAL to minimize locking etc
    DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
    SELECT *
    FROM INSERTED;

    OPEN cur;

    --Get the first row from the INSERTED table
    FETCH NEXT FROM cur
    INTO @CallDate, @clid, ... /* other vars here */, @userfield;

    WHILE @@FETCH_STATUS=0
    BEGIN
        -- run the stored proc
        EXEC Track.dbo.InsertCall @CallDate, @clid, .../* other vars here */, @userfield;
        -- get the next row from INSERTED
        FETCH NEXT FROM cur
        INTO @CallDate, @clid, .../* other vars here */, @userfield;
    END

    CLOSE cur;
    DEALLOCATE cur;

END
GO

Collectives™ on Stack Overflow

SQL Trigger use inserted values to call stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related