Archive for October 5th, 2008

Generating Random Data for SQL Server and MySQL

SQL Server – uses NEWID() in ORDER BY

   1: -- using the AdventureWorks Database
   2: SELECT
   3:     TOP 10
   4:     DepartmentID,
   5:     [Name]
   6: FROM
   7:     HumanResources.Department
   8: ORDER BY
   9:     NEWID()
  10:

MySQL – uses RAND() in ORDER BY

   1: SELECT
   2:   `Code`,
   3:   `Name`
   4: FROM
   5:   `Country`
   6: ORDER BY
   7:   RAND()
   8: LIMIT 10;
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Using DDL Triggers

Sample script below:

   1: -- ==========================================================================
   2: -- Object       : DDLTrigger.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Notes        : 
   6: -- ==========================================================================
   7:
   8: -- DDL Triggers
   9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
  10:
  11: -- DDL Trigger Events
  12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
  13:
  14: -- EVENTDATA
  15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
  16: -- EVENTDATA function returns XML data which contains the information about server 
  17: -- or database events. 
  18:
  19: -- sample EVENTDATA()
  20: /*
  21: <EVENT_INSTANCE>
  22:   <EventType>CREATE_TABLE</EventType>
  23:   <PostTime>2008-10-05T01:23:12.030</PostTime>
  24:   <SPID>55</SPID>
  25:   <ServerName>JUBILEESQL01</ServerName>
  26:   <LoginName>JUBILEEAdministrator</LoginName>
  27:   <UserName>dbo</UserName>
  28:   <DatabaseName>AdventureWorks</DatabaseName>
  29:   <SchemaName>dbo</SchemaName>
  30:   <ObjectName>t</ObjectName>
  31:   <ObjectType>TABLE</ObjectType>
  32:   <TSQLCommand>
  33:     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
  34:                 QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
  35:     <CommandText>CREATE TABLE t
  36:         (
  37:             id int
  38:         )
  39: </CommandText>
  40:   </TSQLCommand>
  41: </EVENT_INSTANCE>
  42:
  43:  */
   44:
  45: -- ---------------------------------------------------------------------
  46: -- database scope
  47: -- SSMS: Database > Programmability > Database Triggers
  48: -- ---------------------------------------------------------------------
  49: USE AdventureWorks
  50: GO
  51:
  52: IF EXISTS(
  53:   SELECT *
  54:     FROM sys.triggers
  55:    WHERE name = N'tr_db_ddl_NoCreateTable'
  56:      AND parent_class_desc = N'DATABASE'
  57: )
  58: BEGIN
  59:     DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
  60: END
  61: GO
  62:
  63: CREATE TRIGGER tr_db_ddl_NoCreateTable
  64: ON DATABASE -- current database
  65: FOR CREATE_TABLE, DROP_TABLE
  66: AS
  67: BEGIN
  68:     SELECT
  69:         'tr_db_ddl_NoCreateTable' 'Trigger',
  70:         'You are not permitted to perform selected action' 'Msg2',
  71:         EVENTDATA() 'EVENTDATA()'
  72:     ROLLBACK
  73: END
  74: GO
  75:
  76: -- test
  77: CREATE TABLE t
  78: (
  79:     id int
  80: )
  81: GO
  82:
  83:
  84: -- ---------------------------------------------------------------------
  85: -- server scope
  86: -- SSMS: Server Objects > Database Triggers
  87: -- ---------------------------------------------------------------------
  88: USE AdventureWorks
  89: GO
  90:
  91: IF EXISTS(
  92:    SELECT *
  93:    FROM sys.server_triggers
  94:    WHERE name = N'tr_srv_ddl_DisplayServerEvents'
  95: )
  96: BEGIN
  97:     DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
  98: END
  99: GO
 100:
 101:
 102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
 103: ON ALL SERVER
 104: FOR CREATE_DATABASE, CREATE_LOGIN
 105: AS
 106: BEGIN
 107:     SELECT
 108:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
 109:         'You are not permitted to perform selected action' 'Msg',
 110:         EVENTDATA() 'EVENTDATA()'
 111:     ROLLBACK
 112: END
 113: GO
 114:
 115: -- test
 116: CREATE DATABASE db
 117: GO

It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS

Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.

The trigger that fails follows:

   1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
   2: ON ALL SERVER
   3: FOR DDL_SERVER_SECURITY_EVENTS
   4: AS
   5: BEGIN
   6:     SELECT
   7:         'tr_srv_ddl_DisplayServerEvents' 'Trigger',
   8:         'You are not permitted to perform selected action' 'Msg',
   9:         EVENTDATA() 'EVENTDATA()'
  10:     ROLLBACK
  11: END
  12: GO
  13:

Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.

DDL Trigger Events

Will continue to dig in; when I find additional details I will post a follow up entry to this one.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Using the OUTPUT clause in SQL Server 2005

   1: -- ==========================================================================
   2: -- Object       : Output.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Origin       : 2008/06/17
   6: -- Last Modified: 2008/10/04
   7: -- Notes        : 
   8: -- ==========================================================================
   9:
  10: -- The OUTPUT clause allows you to display or manipulate rows affected by 
  11: -- INSERT/UPDATE/DELETE
  12: -- Similar to the inserted and deleted tables in a trigger
  13:
  14:
  15: USE AdventureWorks
  16: GO
  17:
  18: IF OBJECT_ID('dbo.DepartmentCopy') IS NOT NULL
  19:   DROP TABLE dbo.DepartmentCopy
  20: GO
  21:
  22: SELECT
  23:     *
  24:     INTO dbo.DepartmentCopy
  25: FROM
  26:     HumanResources.Department
  27:
  28:
  29: -- ---------------------------------------------------------------------
  30: -- display deleted values
  31: -- ---------------------------------------------------------------------
  32: DELETE dbo.DepartmentCopy
  33:     ---------------------------------------------
  34:     OUTPUT 
  35:         deleted.DepartmentID        AS 'Deleted ID',
  36:         deleted.[Name]              AS 'Deleted Name'
  37:     ---------------------------------------------
  38: WHERE 
  39:     DepartmentID > 10
  40:
  41:
  42:
  43: -- ---------------------------------------------------------------------
  44: -- display inserted values PLUS capture inserted values 
  45: -- ---------------------------------------------------------------------
  46: DECLARE @InsertedDepartment TABLE
  47: (
  48:    DepartmentID    smallint,
  49:     [Name]         varchar(50)
  50: )
  51:
  52: INSERT INTO dbo.DepartmentCopy([Name], GroupName, ModifiedDate)
  53:     ---------------------------------------------
  54:     OUTPUT
  55:         inserted.DepartmentID        AS 'Inserted ID',
  56:         inserted.[Name]              AS 'Inserted Name'
  57:     INTO @InsertedDepartment (DepartmentID, [Name])
  58:     ---------------------------------------------
  59: SELECT
  60:     [Name],
  61:     GroupName,
  62:     GETDATE()
  63: FROM
  64:     HumanResources.Department
  65: WHERE
  66:     DepartmentID > 10
  67:
  68: -- display what is captured
  69: SELECT
  70:     *
  71: FROM
  72:     @InsertedDepartment
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`