March 8, 2009 at 1:03 pm
I have a problem that I want to create a trigger for all tables. When a table is inserted, updated or deleted, an event will be saved in table LOGTableEvent. How to create trigger?
and a small question, I created a login trigger:
CREATE TRIGGER [trg_LogonLog]
ON ALL SERVER
FOR LOGIN_EVENTS
AS
BEGIN
INSERT INTO [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
SELECT (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Logon Event' as LogEvent
END
I ran it, the command is successful, but it doesn't save Login Event to table _Log.
What happened?
I need your help. Thanks in advance!
March 8, 2009 at 1:51 pm
you will have to implement it on each and every individual table you want to monitor.
create trALL_yourtable on yourschema.yourtable
for insert, update, delete
as
begin
set nocount on
.....
end
I would advise to log to a local table (in the current database) to avoid any security related issues.
Maybe even use a service broker application (which is very interesting because a message can contain a payload)
Also keep in mind there may be more than one row in your inserted/deleted objects.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 8, 2009 at 2:05 pm
thieuquanghuy (3/8/2009)
FOR LOGIN_EVENTS
The only thing that trigger is going to catch are the CREATE LOGIN, ALTER LOGIN and DROP LOGIN statements.
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
March 8, 2009 at 9:14 pm
GilaMonster,ALZDBA(3/8/2009)
thieuquanghuy (3/8/2009)
Thanks. If I want to catch a log on event, how to write a trigger for it. The information will be saved is logged on name, datetime, succeeded or not, unless, how many times they tried to log on with a wrong password will be saved to a table _LOG.
March 8, 2009 at 9:17 pm
GilaMonster,ALZDBA(3/8/2009)
thieuquanghuy (3/8/2009)
Thanks. If I want to catch a log on event, how to write a trigger for it. The information will be saved is logged on name, datetime, succeeded or not, unless, how many times they tried to log on with a wrong password will be saved to a table _LOG.
March 9, 2009 at 12:53 am
I forgot to reply on your logon trigger.....
Read my little article regarding exactly that:
qa.sqlservercentral.com/articles/Administration/64974/
But If you're on sp3 you shouldn't have the problems I encountered.
I also advise to read todays (2009-09-09) article: http://qa.sqlservercentral.com/articles/Security/66151/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 9, 2009 at 1:58 pm
We have 3rd party application Idera, where you can set up to see any changes in the database (insert, update, delete,select, alter and so on..). Works very good.
March 9, 2009 at 9:19 pm
ALZDBA (3/9/2009)
I forgot to reply on your logon trigger.....Read my little article regarding exactly that:
qa.sqlservercentral.com/articles/Administration/64974/
But If you're on sp3 you shouldn't have the problems I encountered.
I also advise to read todays (2009-09-09) article: http://qa.sqlservercentral.com/articles/Security/66151/
Thanks marina.kudryashov, I wonder if Idera is a shareware.
Thanks ALZDBA, this link qa.sqlservercentral.com/articles/Administration/64974/ is very useful.
and article in http://qa.sqlservercentral.com/articles/Security/66151/ is interesting.
I replace FOR LOGIN_EVENT with FOR LOGON, but I have an error:
Msg 1084, Level 15, State 1, Procedure trg_LogonLog, Line 3
'LOGON' is an invalid event type.
I am very confused what happen. I've read qa.sqlservercentral.com/articles/Administration/64974 carefully.
March 9, 2009 at 9:46 pm
Here's one way:
Declare @sql as nvarchar(MAX)
Set @sql = ''
Select @sql = @sql
+ 'Create trigger ['+TABLE_SCHEMA'].[tr'+TABLE_NAME'_auto]
on ['+TABLE_SCHEMA'].['+TABLE_NAME'] for INSERT,UPDATE,DELETE as
{your-stuff-goes-here}
'
From INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE = 'BASE TABLE'
Print @sql;
EXEC(@sql);
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2009 at 10:19 pm
RBarryYoung (3/9/2009)
Here's one way:
Declare @sql as nvarchar(MAX)
Set @sql = ''
Select @sql = @sql
+ 'Create trigger ['+TABLE_SCHEMA'].[tr'+TABLE_NAME'_auto]
on ['+TABLE_SCHEMA'].['+TABLE_NAME'] for INSERT,UPDATE,DELETE as
{your-stuff-goes-here}
'
From INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE = 'BASE TABLE'
Print @sql;
EXEC(@sql);
Create trigger [dbo].[trspt_fallback_db_auto]
on [dbo].[spt_fallback_db] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
Create trigger [dbo].[trspt_fallback_dev_auto]
on [dbo].[spt_fallback_dev] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
Create trigger [dbo].[trspt_fallback_usg_auto]
on [dbo].[spt_fallback_usg] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
Create trigger [dbo].[trspt_monitor_auto]
on [dbo].[spt_monitor] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
Create trigger [dbo].[trspt_values_auto]
on [dbo].[spt_values] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
Create trigger [dbo].[trMSreplication_options_auto]
on [dbo].[MSreplication_options] for INSERT,UPDATE,DELETE as
begin
insert into [SV].[ManageStudent].[_Log] (UserID,LogDate,LogEvent)
select (Select UserID from [SV].[ManageStudent].[_User] where
UserName = SYSTEM_USER or UserName = USER
) AS UserID,
GETDATE() as LogDate,
'Insert/Update' as LogEvent
This code is my @sql string. when i execute this has error
Msg 156, Level 15, State 1, Procedure trspt_fallback_db_auto, Line 10
Incorrect syntax near the keyword 'trigger'.
I sorry that I wonder if I can understand how Create trigger ['+TABLE_SCHEMA+'].[tr'+TABLE_NAME+'_auto]
on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] works.
Thanks RBarryYoung very much.
March 9, 2009 at 10:32 pm
You've got "begin"'s with no "end"'s. Add in the "end" and you should be fine.
This works by contructing a big string and then executing it. "+" is the string concatenation operator (like "&" in VB).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2009 at 12:58 am
thieuquanghuy (3/9/2009)
...
Thanks marina.kudryashov, I wonder if Idera is a shareware.
Thanks ALZDBA, this link qa.sqlservercentral.com/articles/Administration/64974/ is very useful.
and article in http://qa.sqlservercentral.com/articles/Security/66151/ is interesting.
I replace FOR LOGIN_EVENT with FOR LOGON, but I have an error:
Msg 1084, Level 15, State 1, Procedure trg_LogonLog, Line 3
'LOGON' is an invalid event type.
I am very confused what happen. I've read qa.sqlservercentral.com/articles/Administration/64974 carefully.
It was a nice try ...
Check BOL "CREATE EVENT NOTIFICATION" it leads you to the things you have available.
Also check "Trace Events for Use with Event Notifications" in BOL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2009 at 1:48 am
thieuquanghuy (3/9/2009)
I replace FOR LOGIN_EVENT with FOR LOGON, but I have an error:Msg 1084, Level 15, State 1, Procedure trg_LogonLog, Line 3
'LOGON' is an invalid event type.
I am very confused what happen.
You have SQL 2005 RTM or SP1. The Logon Trigger was added in SP2
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
March 10, 2009 at 9:07 am
ALZDBA (3/10/2009)
thieuquanghuy (3/9/2009)
...
Thanks marina.kudryashov, I wonder if Idera is a shareware.
Thanks ALZDBA, this link qa.sqlservercentral.com/articles/Administration/64974/ is very useful.
and article in http://qa.sqlservercentral.com/articles/Security/66151/ is interesting.
I replace FOR LOGIN_EVENT with FOR LOGON, but I have an error:
Msg 1084, Level 15, State 1, Procedure trg_LogonLog, Line 3
'LOGON' is an invalid event type.
I am very confused what happen. I've read qa.sqlservercentral.com/articles/Administration/64974 carefully.
It was a nice try ...
Check BOL "CREATE EVENT NOTIFICATION" it leads you to the things you have available.
Also check "Trace Events for Use with Event Notifications" in BOL
Thanks ALZDBA, it works.
Thanks GilaMonster. I fixed it.
All of you are expert. 🙂
March 10, 2009 at 2:23 pm
Thank you for the feedback.
HTH
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply