May 23, 2008 at 12:50 am
Hello Guys,
I have a question related to a trigger function.My problem is,
I have a sql query which stores user logs and user's requested queries.I have tried this, but it is not storing any logs to a table.My code is;
CREATE TRIGGER trgLog
ON master..log_tbl FOR insert
AS
BEGIN
Select sp.spid , loginame=rtrim(sp.loginame), sp.hostname, dbname = (case when sp.dbid = 0 then null when sp.dbid <> 0 then db_name(sp.dbid) end),
substring(sql.text, stmt_start/2,CASE WHEN stmt_end<1 THEN 8000 ELSE (stmt_end-stmt_start)/2 END) AS runningSqlText
From master.dbo.sysprocesses sp (NOLOCK)
cross apply sys.dm_exec_sql_text(sp.sql_handle) AS sql inner join sys.dm_exec_connections as sp_con on sp.spid = sp_con.session_id
END
I am trying to create a trigger for this query.My trigger will use SELECT AND INSERT functions to store those logs into a table.
Thank you.
May 23, 2008 at 1:08 am
I think what you need is a scheduled job.
The trigger as you've written it will select a whole bunch of data whenever someone inserts into the master..log_tbl table.
From what you said, I don't think that's what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2008 at 2:05 am
this trigger will not fire until u insert a value in it, so the trigger on this one (master..log_tbl )
want help getting what u want.
..>>..
MobashA
May 23, 2008 at 2:15 am
spid int
loginname nchar(10)
hostname nchar(10)
dbname nchar(10)
runningSqlText varchar(500) this is the DDL Table of mine which I want to store the logs in it.In my database if any user makes any change, I am taking the running sqls with SELECT function and I am tryng to store those running sqls to my DLL table.The actions are simple like, INSERT,UPDATE and DELETE.
* I am trying to use TRIGGER beacause, my database is not saving the records of the user's actions for along time.It is deleting the records after a small time.Because of it, I want to use a trigger to store those actions to my Log_table.
* It has to fire because the records are removed by the database after a small time.Those records are temporarly so, I need to fire the trigger on any data change.
* LoginName( database user name), HostName ( Workstation name ), runningsql (The actions performed by the user like select * from table )
Thanks Alot.
May 23, 2008 at 2:26 am
A trigger fires only when data is changed (in the caseof an insert trigger on insert) in the table that the trigger is on.
Your trigger is only going to fire is someone inserts into the log table.
If you want to catch modifications users make to tables, you need to put the trigger on the table whose modifications you're trying to log, not on the table that stores the logs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2008 at 4:04 am
if u want to track every statments runs by a user u could use traces,
if u want to track drop or creating database object like table u can use database level DDL trigger.
..>>..
MobashA
May 23, 2008 at 4:49 am
Firstly thank you for your reply.If possible,
Could you write a sample code or give an example to me?
Thanks
GilaMonster (5/23/2008)
A trigger fires only when data is changed (in the caseof an insert trigger on insert) in the table that the trigger is on.Your trigger is only going to fire is someone inserts into the log table.
If you want to catch modifications users make to tables, you need to put the trigger on the table whose modifications you're trying to log, not on the table that stores the logs.
May 23, 2008 at 5:31 am
use sql server 2005 help to search for DDL trigger its all u need.
and u can search this site for audit tracing.
..>>..
MobashA
May 23, 2008 at 6:33 am
Books online has some examples of triggers.
Most likely you want something like this (pseudo code only. Will not work as written)
CREATE TRIGGER trg_Audit ON <Name of table to be monitored>
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO master..logtbl (<Column List> )
SELECT < Whatever you want to capture and log >
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply