SQL Server 2005ÖеÄT SQLÔöÇ¿F
DDL´¥·¢Æ÷ DDL Triggers
SQL Server 2005¿ÉÒÔ¾ÍÕû¸ö·þÎñÆ÷»òÊý¾Ý¿âµÄij¸ö·¶Î§ÎªDDLʼþ¶¨Òå´¥·¢Æ÷¡£Ò²¿ÉÒÔΪµ¥¸öDDLÓï¾ä£¨ÀýÈ磺CREAT_TABLE¡¢DROP_TABLEµÈ£©»òÕßΪһ×éÓï¾ä£¨ÀýÈ磺ָ¶¨DDL_DATABASE_LEVEL_EVENTSÏëÒª´¥·¢Æ÷´¥·¢Êý¾Ý¿âËùÓÐDDLʼþ£©¶¨ÒåDDL´¥·¢Æ÷¡£
ÔÚDDL´¥·¢Æ÷ÄÚ²¿£¬¿ÉÒÔͨ¹ý·ÃÎÊeventdata()º¯Êý»ñµÃÓ뼤·¢¸Ã´¥·¢Æ÷µÄʼþÓйصÄÊý¾Ý¡£¸Ãeventdata()º¯Êý·µ»ØÓйØÊ¼þµÄxmlÊý¾Ý¡£
DDL´¥·¢Æ÷ÌØ±ðÓÐÓõķ½°¸°üÀ¨DDL¸ü¸ÄµÄÍêÕûÐÔ¼ì²é¡¢ÉóºË·½°¸ÒÔ¼°ÆäËû·½°¸¡£
´úÂëÑÝʾ£º
USE demo
GO
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR('ûÓÐɾ³ý±íµÄȨÏÞ.', 10, 1)
PRINT '³¢ÊÔÔÚÊý¾Ý¿â' + DB_NAME() + 'ÖÐɾ³ý±í.'
PRINT CONVERT (nvarchar (1000),EventData())
ROLLBACK
GO
-- ²âÊÔ
CREATE TABLE TestDROP(col1 INT)
GO
INSERT INTO TestDROP VALUES(1)
DROP TABLE testdrop
-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT '·¢ÉúDDL LOGIN.'
PRINT CONVERT (nvarchar (1000),EventData())
GO
-- ²âÊÔ
CREATE LOGIN login1 WITH PASSWORD = '123'
ALTER LOGIN login1 WITH PASSWORD = 'xyz'
DROP LOGIN login1
×ܽá
SQL Server 2005ÖеÄTransaction-SQLÔöÇ¿¹¦ÄÜÌá¸ßÁËÓû§ÔÚ±àд²éѯʱµÄ±í´ïÄÜÁ¦£¬Ê¹Óû§¿ÉÒÔ¸ÄÉÆ´úÂëµÄÐÔÄÜ£¬²¢ÇÒÀ©³äÁË´íÎó´¦ÀíÄÜÁ¦¡£SQL Server 2005 ÔÚTransaction-SQLÉÏËù×öµÄ¸Ä½ø·´Ó³ÁËÆä¸üºÃµØÂú×ãÁËANSI-99 SQL¹æ·¶µÄÒªÇóÒÔ¼°¿Í»§µÄÐèÇó¡£ÔÚTransaction-SQLºÍÍйܴúÂëÖ®¼äµÄÑ¡Ôñ¡£
Ïà¹ØÎĵµ£º
¶ÔÓÚÒ»¸öÊý¾Ý¿âÍêÕû±¸·ÝÀ´Ëµ£¬±¸·ÝµÄËٶȺܴó³Ì¶ÈÉÏÈ¡¾öÓÚÏÂÃæÁ½¸öÒòËØ£º¶Á´ÅÅÌÊý¾Ý¡¢ÈÕÖ¾ÎļþµÄÍÌÍÂÁ¿£¬Ð´´ÅÅÌÊý¾ÝÎļþµÄÍÌÍÂÁ¿¡£
ÏÂͼÊDZ¸·Ý¹ý³ÌÖдÅÅ̵ı仯Çé¿ö£º
¶ÁÍÌÍÂÁ¿
¶ÁÍÌÍÂÁ¿µÄ´óСȡ¾öÓÚ´ÅÅ̶ÁÈ¡Êý¾ÝµÄËÙ¶È£¬¶ø´ÅÅ̶ÁÈ¡µÄËÙ¶ÈÓÖÈ¡¾öÓÚÊý¾ÝÎļþÔÚ´ÅÅÌÖеÄλÖá£Òò´Ë£¬Î»ÓÚ²»Í¬ÅÌ·ûÉϲ»Í¬Êý¾Ý¿âÎļþµÄ¶ÁÈ¡Ë ......
²½ÖèÊÇ£º
1£©Ìí¼Ó/ɾ³ý³ÌÐòÖг¹µ×ɾ³ýsql server¡£
2£©½«Ã»ÓÐɾ³ýµÄsql serverĿ¼Ҳɾ³ýµô¡£
3£©´ò¿ª×¢²á±í±à¼Æ÷£¬ÔÚHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session ManagerÖÐÕÒµ½PendingFileRenameOperationsÏîÄ¿£¬²¢É¾³ýËü¡£ÕâÑù¾Í¿ÉÒÔÇå³ý°²×°ÔݹÒÏîÄ¿¡£
4£©É¾³ý×¢²á±íÖиúsql serverÏà¹ØµÄ¼ü¡ ......
1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
¡¡¡¡select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
¡¡¡¡from dba_tablespaces t, dba_data_files d
¡¡¡¡where t.tablespace_name = d.tablespace_name
¡¡¡¡group by t.tablespace_name;
¡¡¡¡
¡¡¡¡2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
¡¡¡¡select tablespace_ ......
TOP ÔöÇ¿¡£¿ÉÒÔÖ¸¶¨Ò»¸öÊý×Ö±í´ïʽ£¬ÒÔ·µ»ØÒªÍ¨¹ý²éѯӰÏìµÄÐÐÊý»ò°Ù·Ö±È£¬»¹¿ÉÒÔ¸ù¾ÝÇé¿öʹÓñäÁ¿»ò×Ó²éѯ¡£
¿ÉÒÔÔÚDELETE¡¢UPDATEºÍINSERT²éѯÖÐʹÓÃTOPÑ¡Ïî¡£
2¡¢¸üºÃµØÌæ»»SET ROWCOUNTÑ¡Ïʹ֮¸üΪÓÐЧ¡£
OUTPUT
1¡¢SQL Server 2005ÒýÈëÒ»¸öеÄOUTPUT×Ӿ䣬ÒÔʹÄú¿ÉÒÔ³åÐÞ¸ÄÓï¾ä(INSERT¡¢UPDATE¡¢DELETE)ÖÐ ......