February 5, 2003 at 11:41 am
Does anyone know what SQL Server does when you issue a CREATE TABLE command?
As some of you may know I have been experimenting on development SQL Servers with placing triggers on system tables.
As a warning to anyone reading this I would not manipulate any system tables anywhere except a non important development environment. I have damaged a database so good that I had to delete it to get rid of the problem.
I placed a trigger (for Insert and Update) on sysobjects and then created a table via Enterprise Manager. The trigger was supposed to insert into a table the name of the object created. The trigger didn't fire.
I opened the table sysobjects in Enterprise Manager and manually added an entry for a table. When I saved that entry, the trigger did fire.
To me, this indicates that the CREATE TABLE statement does not cause what is considered an INSERT or UPDATE to the table sysobjects even though a row appears there for each table created. Or is it just that the CREATE TABLE command will not cause the trigger to fire?
I tried to track in Profiler what happened when I ran a CREATE TABLE command, but it only captured that command and nothing else I could see that related to the sysobjects table.
Does anyone have any thoughts or ideas?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 5, 2003 at 12:39 pm
I read once that if you change for example a column from nullable to non nullable, what EM does, is create a temp table with all the data before changes, changes the original table, and then reinserts all the data saved in the temp table.
Even EM executes other commands for some task, I can't imagine why a T-SQL command won't fire a trigger. Maybe the problem is something different.
February 5, 2003 at 12:51 pm
Microsoft states that triggers are not supported for system tables and has put at least one mechanism in place to block the creation of triggers on system tables.
I am wondering if there are other mechanisms in place to make certain triggers on system tables fail to fire.
I have also placed triggers on sysusers in a database and sysprocesses in the master database. The trigger on sysusers always worked and the one on sysprocesses would never fire.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 5, 2003 at 1:05 pm
Keep in mind that some of the system tables are really virtual. They are created for you on the fly when you query them. The system table syscurconfigs is one such example. There's no language indicating that sysprocesses is, but it might be.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 5, 2003 at 1:13 pm
I think sysprocesses is a virtual table. However, I don't think sysusers and sysobjects are virtual tables. So that shouldn't be a factor in why the trigger on sysusers works and the one on sysobjects doesn't.
I'm guessing that the reason the triggers on sysobjects and sysprocesses don't fire is because the way rows are added to those tables is not considered an INSERT nor an UPDATE and so the triggers can't fire.
Maybe the processing is done out of scope of the trigger so that it can't fire....
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 5, 2003 at 1:37 pm
Well, the CREATE TABLE does a lot of things behind the scenes, so I wouldn't be surprised if it doesn't work in a typical INSERT fashion. Consider that sysobjects, sysindexes, and syscolumns all have to be updated when a CREATE TABLE is executed. Also, page(s) have to be allocated, etc.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 5, 2003 at 1:45 pm
That's the kind of stuff I want to know about! Do you know anywhere where someone discusses these behind the scenes activity or a way I can capture it in Profiler or with some other program?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 5, 2003 at 2:02 pm
I haven't seen anything about that and you won't see it captured in Profiler. I concluded at least those three because they have information required for a new table.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 6, 2003 at 8:14 am
Brian mentions another system table that I was thinking you might try putting a trigger on to capture the creation of a table, syscolumns. I'd be curious if that trigger fires on a table create.
February 6, 2003 at 8:37 am
I'll have to try that. I didn't think to put one there before because the trigger on sysobjects was intended to capture all new objects, not just tables. I simply started testing it by adding a table.
I'll try putting a trigger on syscolumns and see if it will fire.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 6, 2003 at 8:55 am
Keep in mind that this if the trigger is going to fire (don't know, may be same problem when dealing with CREATE TABLE and sysobjects), it'll fire when a user alters an object's columns, too. This means tables, views, stored procedures, and functions. This is probably what you want, but just clarifying to be sure.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 6, 2003 at 3:28 pm
You might try taking a look at the demo version of Lumigent Log Explorer. It was pretty interesting just to see what really happens behind the scenes in the log when system tables are modified in response to DDL statements.
February 6, 2003 at 7:50 pm
Ok here we go, the steps that occurr to create a table with CREATE TABLE statment.
My example is based on this table DDL
create table tbl_x (
idx int identity(1,1) primary key not null,
colx char(4) null
)
1) Generates the Object ID for the new table. This value is in a hidden system table dbo.ALLOCATION, it works similar to an IDENTITY value. It get's the current value and sets the next value (sorry haven't figured out the math behind it yet).
2) Begins the transactions to control rollback if needed.
3) Next the columns are inserted, here we have two columns so the following:
First column in DDL is inserted into syscolumns which is clustered index on (id, colid, number).
Insert of first column into syscolumns generates entry in non-clustered index based on (id, name, number).
Second column in DDL is inserted into syscolumns which is clustered index on (id, colid, number).
Insert of second column into syscolumns generates entry in non-clustered index based on (id, name, number).
Etc. until all columns are added.
4) Next due to the fact I defined idx as a clustered Primary Key an entry to sysindexes for a clustered index is inserted with the values for First and Root set to 0x000000000000 which more or less means no data has been entered yet, otherwise this contains the IAM information for Table and the Root of the new clustered index. Not also that sysindexes is clustered on (id, indid) with id being the Object ID of our table.
5) A mark is issued that the DDL of the table is complete.
6) Now the table name and object id are inserted into sysobjects which is clustered on (id) or the object id of the object.
Which in turn generates two more inserts, one into each non-clustered index. One on (name, uid) and the other on (parent_obj) if one exists the value or 0.
Ok here I am still a bit fuzzy so I cannot elaborate the steps but it will go back to the ALLOCATION table and get the next object id as before. This one will be used for the stats creation for primary key for this table. It will also mark several pages as changed and if they are mixed extents/pages or full. I will take a bit more of a look and may try to elaborate on these if I can.
February 7, 2003 at 12:08 pm
Wow! How or where did you learn all that? Is there a way to see all this behind the scene activity using the tools that ship with SQL Server?
Do you happen to know why the insert into sysobjects does not cause a INSERT TRIGGER on that table to fire?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 7, 2003 at 2:40 pm
Ok some may laugh (no I don't do drugs at all but I do suffer from the fact my brain is constantly going and taking things apart) and some may think I have mastered the inner workings of SQL (I just said I don't do drugs folks, my brain just likes to understand why something works the way it does) and still others may think I am completely off my rocker and have no clue (ok, now this is absured, I can reproduce the same results repeatedly without fail). I am currently working on a log write up on serveral things and this is one of them (anyone have any suggestions on a publisher who might be interested??? ANd How to go about presenting to them???).
Anyway all I used was DBCC LOG (it is undocumented so it is one of those server-yo-self features).
Now based on the sequence I gave you, you can follow it as a map and hopefully get an idea of what I am talking about, but let's set things up first.
First we need a clean Transaction Log. Which I make sure of by stopping and starting my SQL test instance.
ALso, make sure the DB using for testing is set with Full as Recovery model if SQL 2K or do not have Trunc on Chk Point turned on if SQL 7
Then in QA run
DBCC LOG ('TestDBName', 3)
And you should return two rows of which the key field is the operation field. They should read
LOP_BEGIN_CKPT and LOP_END_CKPT
If anything else is in the try using BACKUP LOG with TRUNCATE_ONLY and restarting.
Now run the create statement I posted and then rerun the DBCC LOG item above.
You will get a lot of output which will follow roughly the same output I listed. Just remember to make sure this is your only action that has taken place since the check for LOP_BEGIN_CKPT and LOP_END_CKPT and remember to skip those in this new output.
Won't go into too much detail here as it is quite a lot to explain what you are seeing but follow my previous details and you should be able to stumble thru it.
Now as for the reason Triggers on system tables do not fire. My only thoughts are it may be similar to why Procs that start with sp_ in the master db that are marked as system objects (see article here http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp). There must be a built in piece that when is a system object do not check for trigger to be fired.
Hope that all helps a bit.
Edited by - antares686 on 02/07/2003 2:41:20 PM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply