Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

October, 2004

Posts
  • Benjamin Wright-Jones

    General ravings...IDW9....

    • 1 Comments

    Hello again I'm pleased to announce that the latest build of SQL Server 2005 includes two new T-SQL commands, INTERSECT and EXCEPT.  I'll talk about these a little later. I've just been on a great 2005 training course delivered by Niels Berglund of developmentor.  Niels has introduced me to some great features which I'll talk about in this blog.  I'm going to run through the labs again as I missed some of the slides on http endpoints and service broker.  There are so many cool things in SQL Server 2005 that its difficult to know where to begin.  SQLCLR is such a great enhancement P> I'll post again soon....

  • Benjamin Wright-Jones

    SQL Server 2005 Extended Triggers

    • 4 Comments

    Ok, I am impressed with everything in SQL Server 2005, especially extended triggers to capture DDL actions.  I was playing around with these today and I can see how this will be a nice feature, especially for audit trails.  I've included a simple script below.

    -- create sample db
    create
    database trigtestdb
    go

    use trigtestdb
    go
    create table tblextrig (eventid int identity, eventdata xml)
    go

    -- create event driven trigger
    create trigger trig_ddl
       
    on database
       
    for create_table, drop_table, alter_table
    as
       
    insert tblextrig values (eventdata())
    go

    -- do some ddl
    create table ben (id int)
    drop table ben
    create table ben (id int)
    go

    -- check if DML events have been logged by trigger
    select eventid, eventdata from tblextrig
    go

    -- clean up
    use master
    go

    drop database trigtestdb

     

Page 1 of 1 (2 items)