By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,836 Members | 2,097 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,836 IT Pros & Developers. It's quick & easy.

Help with trigger

P: n/a
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 =----
Aug 10 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
MM (me@home.com) 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

Aug 10 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.