create table with TRIGGER

I need an Insert trigger to generate new tables when i insert a new record..
new tables will be "MasterSub_[ID]" where ID is the id of new inserted
parent record..
I also want a delete triger to remove child table if it has no data in it..
Any help plz'This is a very bad design and will likely perform poorly. Why do you need
to create tables every time you insert rows?
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:uFTjXyR7FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I need an Insert trigger to generate new tables when i insert a new
>record..
> new tables will be "MasterSub_[ID]" where ID is the id of new inserted
> parent record..
> I also want a delete triger to remove child table if it has no data in
> it..
> Any help plz'
>
>

Create procedure or trigger to auto generate string ID

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Royi dont think there is any "autogenerated string" you can only have a numeric column...you need to write your own script to get the next value if you decide to use strings ...

hth|||Do not use a string in your DB, rather cast it to a string when you need to use it.|||Thanks you for reply!!

I know there is no autogenerate string. But I need to use string value as primary key. When new record inserted, e.g. string 'A0001' should be generated automatically.

U said that script can help. Could u briefly tell me some steps by how to write??

Thanks for help!!!|||i got some code in my pc at work place...it will take a value and give you the next number/string..

xample:
if you pass 1 to it, it will return 2..
if you pass A1 it will return A2 and return Abcde45 if you pass Abcde44...
the only drawback is it wont work if you try to use a1a...as long as its string followed by number...it works...
i can share it here on monday if you are interested...

hth|||You can write a trigger on that table to fire on INSERT, and that will fill in the next number in your table.

Look up Triggers in Books Online|||Thanks you for reply!!

I could not find books for writing this procedure.

Could u share the code for what u said??

Thanks a lot!!!!!|||Look for SQL Server Books Online - it's a free reference. Do a Google search, I don't know the URL.

Create One Trigger For Both Update and Delete

hi,
CAn i have one trigger for both Update and Delete
Delete Trigger
-------
create Trigger [tr_delete_user_log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end

Trigger Update
-------
CREATE Trigger [tr_update_user_log]
on [dbo].[user_log] for update
as
begin
insert into z_user_log select * from deleted
end

Can i have one trigger instead of these Triggers ..On 30 Apr 2007 06:02:57 -0700, satish wrote:

Quote:

Originally Posted by

>hi,
>CAn i have one trigger for both Update and Delete

(snip)

Quote:

Originally Posted by

>Can i have one trigger instead of these Triggers ..

Hi satish,

Yes.

CREATE Trigger [tr_update_delete_user_log]
on [dbo].[user_log] for update, delete
as
begin
insert into z_user_log select * from deleted
end

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||satish (satishkumar.gourabathina@.gmail.com) writes:

Quote:

Originally Posted by

CAn i have one trigger for both Update and Delete
Delete Trigger
-------
create Trigger [tr_delete_user_log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end

As Hugo said, you can. Permit me to point that your example exhibits
two cases of bad practice:

o INSERT without a values list. If someone adds a column to user_log,
the INSERT statement will fail.
o SELECT *. While convenient for ad hoc queries, it's bad in production
code. In this example - if someone adds or removes a column - or
just changes the column order, the INSERT statement will fail. SELECT *
also make it more difficult to find where different columns are
actually used.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 30 Apr 2007 21:31:28 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

Permit me to point that your example exhibits
>two cases of bad practice:

(snip)

Hi Erland,

Thanks for stepping in. I new feel so bad for not mentioning that
myself. Please remind me not to reply when tired in the future :-)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

create database command in trigger

Hi,

As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.

I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.

I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger

CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'

Thanks,

IanPlease explain something more about your process|||Hi,

The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.

I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger

declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt

I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.

Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.

I hope this is some use.

Ian