?
Solved

Passing parameters to an SPROC from Inserted  and Deleted in a Trigger

Posted on 2006-05-17
9
Medium Priority
?
336 Views
Last Modified: 2012-05-05
I'm trying to put a trigger on tables in my db in order to log activity. I am passing such things to the Journal_Items table as the table name, recordid, user, etc. I created this trigger successfully, but when I insert a record to the table, I get an error saying that the parameter @USERID was not supplied,

This is the trigger:

ALTER TRIGGER [trgClientInsert] ON [dbo].[Client]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @action VARCHAR(20)
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    SET @action = 'Record Updated'
ELSE
IF EXISTS(SELECT * FROM inserted)
    SET @action = 'New Record'
ELSE
    SET @action = 'Record Deleted'

exec adv_AddJournalItem select create_userid, portalid, client_name, 'Client', Clients_id, @action
From Inserted

This is the SPROC that is being called:

Create  procedure dbo.adv_AddJournalItem

      @UserId          int,
      @PortalId      int,
      @Description    varchar(80),
      @TableName      varchar(50),
      @RecordId              int,
      @Type            varchar(20)

as
declare @TableId int
    SELECT
     @TableId = tableid
      FROM tablenames
     WHERE
      table_name = @TableName

insert into Journal_Items (
      Reference_TableId,
      Create_UserId,
      Create_Date,
      PortalId,
      Description,
      Reference_Type,
      Reference_RecordId
)
values (
      @TableId,
      @UserId,
      getdate(),
      @PortalId,
      @Description,
      @Type,
      @RecordId
)

select SCOPE_IDENTITY()
0
Comment
Question by:pauldes
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16702535
pauldes,
> exec adv_AddJournalItem select create_userid, portalid, client_name, 'Client', Clients_id, @action
> From Inserted

in this way you can't call the sp

u need to store the values in a variable and using this vars call the sp

DECLARE  @UserId         int,
     @PortalId     int,
     @Description    varchar(80),
       @RecordId             int,
     @Type          varchar(20)

SELECT@userID = create_userid,
            @PortalID = portalid,
            @Description = client_name,
            @RecordId     = Clients_id
 From Inserted

 exec adv_AddJournalItem @userID, @portalid, @Description, 'Client', @RecordId, @action
0
 

Author Comment

by:pauldes
ID: 16703025
Cool
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16704634
Keep in mind that if seven records get inserted into the table, the INSERTED table will hold seven records, and this statement will populate your variables with only one set of those records (randomly):


SELECT@userID = create_userid,
            @PortalID = portalid,
            @Description = client_name,
            @RecordId     = Clients_id
 From Inserted
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16705460
As nmcdermaid has pointed out, you could have multiple rows in Inserted.  So you need to CURSOR through all the rows and execute the Stored Procedure for each row.
0
 

Author Comment

by:pauldes
ID: 16711813
OK, now we're way over my head. Where's a good place to start reading about this?

Hey what about this:
SELECT@userID = create_userid,
            @PortalID = portalid,
            @Description = client_name,
            @RecordId     = Clients_id
 From Inserted where inserted.clients_id = clients_id(from the record the trigger is executing on?)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16713750
>> from the record the trigger is executing on

There might be ten records.


If you run this on a table with an insert trigger:

INSERT INTO ATable (Col1,Col2)
VALUES (1,2)

Then one record goes into the table, the trigger is fired, and INSERTED contains one record.



If you run this on a table with an insert trigger:

INSERT INTO ATable (Col1,Col2)
SELECT Field1, Field2
FROM ATableWithTenRecords

Then ten records go into the table, the trigger is fired and INSERTED contains ten records.


0
 

Author Comment

by:pauldes
ID: 16714098
OK, then, I'll go back to my statement that I'm in over my head.

Where can I read up on how to accurately accomplish what I am trying to do in logging database activity or follow the proposed solution above to execute on the following:

> So you need to CURSOR through all the rows and execute the Stored Procedure for each row

Thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16714921
Something like this perhaps:

ALTER TRIGGER [trgClientInsert] ON [dbo].[Client]

FOR INSERT, UPDATE, DELETE

AS

DECLARE @create_userid integer,
      @portalid integer,
      @client_name varchar(80),
      @Clients_id integer,
      @action VARCHAR(20)


IF EXISTS(SELECT 1 FROM inserted)
      IF EXISTS(SELECT 1 FROM deleted)
          SET @action = 'Record Updated'
      ELSE
          SET @action = 'New Record'
ELSE
    SET @action = 'Record Deleted'

If @action != 'Record Deleted'
      Declare MyCursor CURSOR FOR
      Select      create_userid, portalid, client_name, Clients_id
      From      Inserted

Else
      Declare MyCursor CURSOR FOR
      Select      create_userid, portalid, client_name, Clients_id
      From      Deleted


OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @create_userid, @portalid, @client_name, @Clients_id
WHILE @@FETCH_STATUS = 0
   BEGIN
      exec adv_AddJournalItem @create_userid, @portalid, @client_name, 'Client', @Clients_id, @action
      FETCH NEXT FROM MyCursor INTO @create_userid, @portalid, @client_name, @Clients_id
   END

CLOSE MyCursor
DEALLOCATE MyCursor
0
 

Author Comment

by:pauldes
ID: 16716548
WOW, Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question