Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Arrow Unanswered: Creating Multiple Triggers is same sql script

    I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

    CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
    FOR DELETE
    AS

    INSERT into PO_Back
    SELECT *, host_name(), suser_name(), getdate()
    FROM deleted
    GO

    CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
    FOR DELETE
    AS

    INSERT into receipt_Back
    SELECT *, host_name(), suser_name(), getdate()
    FROM deleted
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that's the way to do it...(except for the SELECT * bit)

    http://weblogs.sqlteam.com/brettk/ar...4/22/1272.aspx

    Are you getting an error?

    select * is dangerous btw.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2004
    Posts
    10
    It stops at the second create trigger and indicates create trigger not valid. Should the syntax I have work?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    works for me....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE PO_BOL(Col1 int)
    CREATE TABLE receipt(Col1 int)
    GO
    
    CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL 
    FOR DELETE 
    AS
    
    INSERT into PO_Back
    SELECT *, host_name(), suser_name(), getdate() 
    FROM deleted
    GO
    
    CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt 
    FOR DELETE 
    AS
    
    INSERT into receipt_Back
    SELECT *, host_name(), suser_name(), getdate() 
    FROM deleted
    GO
    
    DROP TABLE PO_BOL
    DROP TABLE receipt
    GO
    Couple of things...lose SELECT *, Make sure you supply the column list for the insert...other than that it all looks good
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2004
    Posts
    10
    Thanks for the help! I got it to work...... Thanks again.

    Another question:

    I'm using VB to open a direct connection to SQL SERVER 2000. The AnsiNPW=off in the connection doesn't work. Any ideas? I had to create my tables in SQL server with SET ANSI_PADDING OFF to get the spaces trimmed.

    Any thoughts?

    JGS

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'll tell anone who'll listen that I've forgot all my VB...otherwise they might make me build interfaces...

    Just talking about it and I feel all dirty...

    How is the table defined?

    char or varchar??
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Posts
    10
    Your on to it! The fields are Varchar. I tried changing the fields to Char but AnsiNPW still doesn't seem to do anything in the connection string. Have you heard of any bug that AnsiNPW doesn't work?

Posting Permissions

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