Help with trigger

[MM]

I currently have a trigger on a table which works fine.

It performs some audit trail functions.

************************************************** ****
CREATE TRIGGER trg_1 ON [dbo].[LINE14_PROD] 
FOR INSERT, UPDATE, DELETE
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
 
 SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
 
 INSERT INTO tbl_inputbuffer
 EXEC (@ExecStr)

    insert tblWho execute sp_who @@spid

END
************************************************** ****

It inserts the results of the command 'DBCC INPUTBUFFER (@@SPID) into table tbl_InputBuffer
It inserts the results of the command 'spWho @@SPID) into table tblwho

What I would like it to do is make one record in one table (with the fields of both tables).

How can I rewrite the trigger to retrieve the results of each command and then insert the data as one record into a table?

EXISTING Table - tbl_InputBuffer:
CREATE TABLE [dbo].[tbl_inputbuffer] (
    [EventType] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Parameters] [int] NULL ,
    [EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


EXISTING Table - tblWho:
CREATE TABLE [dbo].[tblWho] (
    [spid] [int] NULL ,
    [ecid] [int] NULL ,
    [status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [loginname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [blk] [int] NULL ,
    [dbname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cmd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

NEW Table -  tblAudit:
CREATE TABLE [dbo].[tblAudit] (
    [spid] [int] NULL ,
    [ecid] [int] NULL ,
    [status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [loginname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [blk] [int] NULL ,
    [dbname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cmd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EventType] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Parameters] [int] NULL ,
    [EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Thanks in advance,

MM
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Erland Sommarskog]

MM ([e-mail]) writes:

I currently have a trigger on a table which works fine.<br>
<br>
It performs some audit trail functions.<br>
<br>
************************************************** ****<br>
CREATE TRIGGER trg_1 ON [dbo].[LINE14_PROD]&nbsp; <br>
FOR INSERT, UPDATE, DELETE <br>
AS<br>
BEGIN<br>
&nbsp;SET NOCOUNT ON<br>
&nbsp;<br>
&nbsp;DECLARE @ExecStr varchar(50), @Qry nvarchar(255)<br>
&nbsp;<br>
&nbsp;SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'<br>
&nbsp;<br>
&nbsp;INSERT INTO tbl_inputbuffer <br>
&nbsp;EXEC (@ExecStr)<br>
<br>
&nbsp;&nbsp;&nbsp; insert tblWho execute sp_who @@spid<br>
<br>
END<br>
<br>
It inserts the results of the command 'DBCC INPUTBUFFER (@@SPID) into
table tbl_InputBuffer<br>
It inserts the results of the command 'spWho @@SPID) into table tblwho<br>
<br>
What I would like it to do is make one record in one table (with the
fields of both tables).<br>

You could get the data into temp tables and from these insert into
the target table. An alternative would be to get the information
from sp_who directly from sysprocesses. I think the latter is perferable,
as then you don't need the temp table, and I have had bad experiences
of temp tables in triggers.

However, I would be hesitant to do this. Triggers should be fast,
as they execute in the context of a transaction. I don't really like
running INPUTBUFFER or querying sysprocesses from a trigger. sysprocesses
is a virtual table that is constructed from internal server structures.
How much resources it takes to get the information I don't know.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp