SQL Server Performance Forum – Threads Archive

Create table in trigger

At the first time ‘insert’ happens on another table a new table should be created (using trigger) in order to insert that new record. All the times after that – just inserting a new data without creating tables. How should it be written? thanks in advance

Looks like you know beforehand what the design of the table is, and what it will be called. So why not just create the table in the database?

This table should be created only in case that â€˜insert#%92 takes place. So, is it possible to create a table as a part of trigger code?

If you must, insert something along these lines: IF OBJECT_ID(‘MyNewTable’) IS NULL
BEGIN
CREATE TABLE MyNewTable …………..
END Not sure that I would be happy with this kind of functionality, period. What function does the new table serve outside the trigger? How long do any of these tables exist?

Thanks, Addriaan for your respond. Can you go a little deeper and explain to me why it is bad to create a table within a trigger?

It is not necessarily bad to create a table in a trigger – about the only place that you cannot create a table is in a UDF, so SQL Server isn’t terribly restrictive about this. With databases, you really want to know ahead of time what objects you’re dealing with. And what you’re doing is basically hiding objects from view, and if someone else creates a table in the database with the same name but a different data structure, then you’re in trouble. I say "someone else" but of course it could also be another trigger creating the table on the fly. If it’s a permanent object after it’s created, and the design is known beforehand, then you’re not gaining anything by creating the table at the last moment: empty tables do not take up much space or server resources. If the design is not known beforehand, then perhaps you’re dealing with weak or even bad normalization.

Got it, Adriaan. Thanks a lot.
Alex.

]]>