Creating Tables with Triggers

I need an Insert trigger to create a set of tables to my database when i add
new record in the top level master table (horizontal partitioning
denormalization)..
Is this possible using trigger or procedure or even function' and how'
any help appreciated.I'd doing such things by using stored procedure rather within a trigger
Identify that row has beed added and call SP to create a table
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||As I said in my other post:
1) Don't multi-post.
2) This is a bad design.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need an Insert trigger to create a set of tables to my database when i
>add new record in the top level master table (horizontal partitioning
>denormalization)..
> Is this possible using trigger or procedure or even function' and how'
> any help appreciated.
>|||1) I said sorry for posting in 2 groups..
2) there was no reply to my first thread and i'm in need to help
3) Why u think it's a bad design' How gcould i design something better'
hope u could help
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
> As I said in my other post:
> 1) Don't multi-post.
> 2) This is a bad design.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||How could i write a procedure to create table "Table_[ID]" Where id is a
paramter..
Also i must use trigger anyway to call this proccedure right'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uS5H77b7FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I'd doing such things by using stored procedure rather within a trigger
> Identify that row has beed added and call SP to create a table
>
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:OI%23fdSb7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||You'll have to be patient. Participation in these groups is voluntary. If
you want immediate help, then open a ticket with PSS.
As for the design, you will have a proliferation of tables, which will be
difficult to control. Why is it that you need all of these tables? Why
can't you have a single table with a column that differentiate? How about
providing a clear business spec - not a program spec?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
> 1) I said sorry for posting in 2 groups..
> 2) there was no reply to my first thread and i'm in need to help
> 3) Why u think it's a bad design' How gcould i design something better'
> hope u could help
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uT635Kc7FHA.1000@.tk2msftngp13.phx.gbl...
>|||Thanx for inform me about PSS ticket and sorry for multi-post.
I need to have many tables coz one table will hv more than 6,000,000 record
with 2 text fields one of them is type of "text". which mean searching is so
slow.. so partitioning tables will make each table contain 100,000 to
600,000 record which give reliable search preformance specially with joins
and relations..
The business is database of counties laws and rules, and logically one will
search laws by country, so i partition my tables depend on the the country.
What do u think' do you have better idea'
P.S.. My text is in Arabic so Full Text indexing do not help..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
> You'll have to be patient. Participation in these groups is voluntary.
> If you want immediate help, then open a ticket with PSS.
> As for the design, you will have a proliferation of tables, which will be
> difficult to control. Why is it that you need all of these tables? Why
> can't you have a single table with a column that differentiate? How about
> providing a clear business spec - not a program spec?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:eOhRFGd7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server can handle tables of billions of rows. Typically, you'd place
the text in a separate table, with a FK to the parent. You can place a
Country and County FK column on the parent and index those.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Thanx for inform me about PSS ticket and sorry for multi-post.
> I need to have many tables coz one table will hv more than 6,000,000
> record with 2 text fields one of them is type of "text". which mean
> searching is so slow.. so partitioning tables will make each table contain
> 100,000 to 600,000 record which give reliable search preformance specially
> with joins and relations..
> The business is database of counties laws and rules, and logically one
> will search laws by country, so i partition my tables depend on the the
> country.
> What do u think' do you have better idea'
> P.S.. My text is in Arabic so Full Text indexing do not help..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejgvjdd7FHA.2716@.TK2MSFTNGP11.phx.gbl...
>|||this drive to 2 big tables the parent one, and text table one.. this is the
current database structure which need 4 -5 min to search in the text..
this is time isn't acceptable so i will partitioning it to tables which cut
search time to 15 - 30 second (i tested it)
this is big improvment in time.
what do u think'
also could you plz tell me how could i use triggers to create these tables'
thanx
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eiwD66d7FHA.2012@.TK2MSFTNGP14.phx.gbl...
> SQL Server can handle tables of billions of rows. Typically, you'd place
> the text in a separate table, with a FK to the parent. You can place a
> Country and County FK column on the parent and index those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gk6Brd7FHA.2432@.TK2MSFTNGP10.phx.gbl...
>|||> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:%23gWgQfe7FHA.1416@.TK2MSFTNGP09.phx.gbl...

> this drive to 2 big tables the parent one, and text table one.. this is
> the current database structure which need 4 -5 min to search in the text..
> this is time isn't acceptable so i will partitioning it to tables which
> cut search time to 15 - 30 second (i tested it)
Correct indexing will serve you much better than partitioning the table.
Create an index on whatever attribute you would otherwise partitrion the
table by and search on that as well as your other criteria. If "search in
the text" means a free text search then also take a look at the "Full-text
indexing" topics in Books Online. If you look at your earlier thread on this
topic I also made some other suggestions.
David Portas
SQL Server MVP
--

Creating Horizontal Partition

From a previous post I got an example of creating a Horizontal Partition. I have altered the code in an attempt to understand why the partition I want fails on insert. The altered script is:

create table Table_A_Jan (Col1 int not null, Col2 char(2) not null CHECK (col2 = 'RL')--CHECK (Col2 >= cast('20060101' as datetime) and Col2 < cast('20060201' as datetime))--,--Col3 char(5) not null, CONSTRAINT [PKJan] PRIMARY KEY CLUSTERED ([Col1], [Col2]/*, [Col3] */) WITH FILLFACTOR = 95 ON [PRIMARY] )

create table Table_A_Feb (Col1 int not null,Col2 datetime not null CHECK (col2 = 'RP') --(Col2 >= cast('20060201' as datetime) and Col2 < cast('20060301' as datetime))--,--Col3 char(5) not null, CONSTRAINT [PKFeb] PRIMARY KEY CLUSTERED ([Col1], [Col2]/*, [Col3]*/) WITH FILLFACTOR = 95 ON [PRIMARY] )

CREATE VIEW A AS SELECT * FROM Table_A_Jan

UNION ALL

SELECT * FROM Table_A_Feb

Then the insert

INSERT INTO A (Col1, Col2/*, Col3*/) VALUES (45, 'RL'--'2006-01-21 23:55:34.000'--, '00073' )

And the resulting error message

because the primary key of table '[AAAA].[dbo].[Table_A_Jan]' is not included in the union result.

As long as I keep Col2 as date time everything works. Why does it fail when I want the CHECK contraint to be equal to a particular string?

(Sorry about the font issue don't get what is going on here)

Andrew.

This forum where you will seek help for Analysis Services related questions. I know your quest started from exploring solutions for Analysis Services application, but looks like you missing some basic knowlege on relational databases (SQL Server and likes). I suggest you get yourself a good book or tutorial on how to work with SQL.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.