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¸·Ý¹ý³ÌÖдÅÅ̵ı仯Çé¿ö£º
¶ÁÍÌÍÂÁ¿
¶ÁÍÌÍÂÁ¿µÄ´óСȡ¾öÓÚ´ÅÅ̶ÁÈ¡Êý¾ÝµÄËÙ¶È£¬¶ø´ÅÅ̶ÁÈ¡µÄËÙ¶ÈÓÖÈ¡¾öÓÚÊý¾ÝÎļþÔÚ´ÅÅÌÖеÄλÖá£Òò´Ë£¬Î»ÓÚ²»Í¬ÅÌ·ûÉϲ»Í¬Êý¾Ý¿âÎļþµÄ¶ÁÈ¡Ë ......
ÔÚSQL Server ÖвåÈëÒ»ÌõÊý¾ÝʹÓÃInsertÓï¾ä£¬µ«ÊÇÈç¹ûÏëÒªÅúÁ¿²åÈëÒ»¶ÑÊý¾ÝµÄ»°£¬Ñ»·Ê¹ÓÃInsert²»½öЧÂʵͣ¬¶øÇһᵼÖÂSQLһϵͳÐÔÄÜÎÊÌâ¡£ÏÂÃæ½éÉÜSQL ServerÖ§³ÖµÄÁ½ÖÖÅúÁ¿Êý¾Ý²åÈë·½·¨£ºBulkºÍ±íÖµ²ÎÊý(Table-Valued Parameters)¡£
ÔËÐÐÏÂÃæµÄ½Å±¾£¬½¨Á¢²âÊÔÊý¾Ý¿âºÍ±íÖµ²ÎÊý¡£
´úÂëÈçÏÂ:
--Create D ......
TOM´óÊåµÄµ÷ÓÅÊéÖÐ˵¹ýÒ»¾ä»°£¬¾ßÌåÓ¢ÎľÍÍüÁË£¬´ó¸ÅÒâ˼¾ÍÊÇ£ºÈç¹ûÓÐÈËÈÃÎÒд±¾ÔõÑùÈÃORACLEÐÔÄÜ×îÂýµÄÊéµÄ»°ÎһὫȡÏû°ó¶¨±äÁ¿(bind variable)×öΪÊéµÄµÚÒ»ÕºÍ×îºóÒ»ÕÂ(ËûµÄÒâ˼ÊÇËûºÜÓÐÓÄĬ~~!)£¬¿É¼û°ó¶¨±äÁ¿µÄÖØÒªÐÔ¡£
°ó¶¨±äÁ¿´ó¶àÓÃÔÚOLTP(online transaction process)ÖУ¬ÔÚOLAP(online analizy process)ÖоÍû± ......
¿âλ »õÎï±àºÅ ¿â´æÊý
1 0101 50
1 0102 60
1 0103 50
2 0101 90
2 0103 100
2 0111 30
3 0101 12 ......