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
 
 DECLAR E @ExecStr varchar(50), @Qry nvarchar(255)
 
 SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
 
 INSERT INTO tbl_inputbuffer
 EXEC (@ExecStr)

  &nb sp; 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] (
  &nb sp; [EventType] [nvarchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [Parameters] [int] NULL ,
  &nb sp; [EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]


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

NEW Table -  tblAudit:
CREATE TABLE [dbo].[tblAudit] (
  &nb sp; [spid] [int] NULL ,
  &nb sp; [ecid] [int] NULL ,
  &nb sp; [status] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [loginname] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [hostname] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [blk] [int] NULL ,
  &nb sp; [dbname] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [cmd] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [EventType] [nvarchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
  &nb sp; [Parameters] [int] NULL ,
  &nb sp; [EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_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]

Re: Help with trigger

MM (me@home.com) writes:[color=blue]
> 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;DECLAR E @ExecStr varchar(50), @Qry nvarchar(255)<b r>
> &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;&nb sp; 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>[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp