Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Trigger related question

    Hi all,

    Can a table be created within a trigger? I looked in books online and found the list of transact-sql statements that are not allowed in a trigger. Create table is not one of them, so I assume that it can be done within a trigger. Correct? Thanks for your help.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    basically, yes (but why should it?):

    [code]
    use monkey
    go

    set nocount on

    create table tab1 (
    myCol001 integer not null
    )
    go

    create trigger tr_tab1 on tab1 for insert as
    create table tab2 (
    myCol002 integer not null
    )
    insert into tab2 (myCol002) values (3)
    select * from tab2
    drop table tab2
    go

    insert into tab1 (myCol001) values (1)

    drop table tab1
    go
    [code]

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't believe changes to the schema are allowed by triggers.

    [Edit]
    Hmph. Maybe so. My bad for assuming that something can't be done just because it shouldn't be done...
    Last edited by blindman; 12-03-04 at 12:34.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well-well-well, OF COURSE it can be done (siding with Lindman in respect to WHY though):

    use monkey
    go

    set nocount on

    create table tab1 (
    myCol001 integer not null
    )
    go

    create trigger tr_tab1 on tab1 for insert as
    --HELLO, it never worked, since 6.5!!!
    /*
    create table tab2 (
    myCol002 integer not null
    )
    */
    -- same reason here --> insert into tab2 (myCol002) values (3)
    select * into tab2 from (select myCol001=3) x
    drop table tab2
    go

    insert into tab1 (myCol001) values (1)

    drop table tab1
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •