SQL2005 Real-time Monitoring database version control SQL triggers _ database other

Source: Internet
Author: User
Copy Code code as follows:

/******
Objects: Ddltrigger [Trig_dbversioncontroller]
Script Date: 09/05/2008 15:50:16
Role: Database Version control
******/
IF EXISTS (SELECT * from sys.triggers WHERE name = N ' Trig_dbversioncontroller ' and parent_class=0)
DROP TRIGGER [Trig_dbversioncontroller] on DATABASE
Go

CREATE TRIGGER [Trig_dbversioncontroller]
On DATABASE
For Create_table, alter_table, Drop_table,
Create_function, Alter_function, Drop_function,
Create_index, Alter_index, Drop_index,
Create_procedure, Alter_procedure, Drop_procedure,
Create_trigger, Alter_trigger, Drop_trigger,
Create_view, Alter_view, Drop_view
As
SET NOCOUNT on
DECLARE @data as XML
DECLARE @clientUser as nvarchar (128)
DECLARE @spid as nvarchar (128)
DECLARE @serverName as nvarchar (128)
DECLARE @dbName as nvarchar (128)
DECLARE @dbid as int
DECLARE @objName as nvarchar (512)

SELECT @data =eventdata ()
SELECT @spid = @data. Value (' (/event_instance/spid) [1] ', ' nvarchar (128) ')
SELECT @serverName = @data. Value (' (/event_instance/servername) [1] ', ' nvarchar (256) ')
SELECT @dbName = @data. Value (' (/event_instance/databasename) [1] ', ' nvarchar (128) ')
SELECT @objName = @data. Value (' (/event_instance/objectname) [1] ', ' nvarchar (128) ')

SELECT @dbid = dbid from sys.sysdatabases
WHERE name = @dbName

--Get the machine name of the client

SELECT @clientUser =hostname from master. sysprocesses WHERE spid= @spid
--Add version record
INSERT into DBController.dbo.tbl_version_details
(Post_computer_name, Sys_dbid, UID, [schema],
Posttime, EventType, ObjectType, ObjectName, CommandText, remark)
VALUES
(@clientUser, @dbid,
@data. Value (' (/event_instance/loginname) [1] ', ' nvarchar (256) '),
@data. Value (' (/event_instance/username) [1] ', ' nvarchar (256) '),
@data. Value (' (/event_instance/posttime) [1] ', ' datetime '),
@data. Value (' (/event_instance/eventtype) [1] ', ' nvarchar (128) '),
@data. Value (' (/event_instance/objecttype) [1] ', ' nvarchar (128) '),
@objName,
@data. Value (' (/event_instance/tsqlcommand/commandtext) [1] ', ' nvarchar (max) '),
''
)

Go
SET ansi_nulls off
Go
SET QUOTED_IDENTIFIER OFF
Go
ENABLE TRIGGER [Trig_dbversioncontroller] on DATABASE
Go

It's trigger.

The following are the stored libraries and table structures
Provide pictures

The picture above can be clearly seen in the table structure
Everybody write out the SQL of the table

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.