Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Trigger fires in one db and populate another db

    hi,

    I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.

    a
    win server 2003 standard Edition
    sql Server 2000
    db:aaa
    table: a_aaa
    column:a_aaa_a

    b
    win server 2003 standard Edition
    sql Server 2000
    db:bbb
    table:b_bbb
    column: b_bbb_b

    It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera......
    Can anyone help?

    Thanks in advance.



    Code:
    CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA] 
       
    FOR  INSERT, UPDATE
    AS
    
    BEGIN
    	SET NOCOUNT ON;
    
        	DECLARE @EndDate as DATE
        	DECLARE @CompCode as VARCHAR(15)
        	DECLARE @PhoneGMSM as VARCHAR(10)
        	DECLARE @PhoneALERT as VARCHAR(10)
    
    	SELECT  @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
    	SELECT  @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
    	
    	UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111" 
    	WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
    	
    	
    	WHERE:
    	
    	USER_DATA_DEVIQ = b_bbb_b
    	AUSADFORMULA02 = bbb
    	QDB_KS = b_bbb
    	USER_DATA = a_aaa_a
    	AUSSMEDEVIQ1 = aaa
    	QDB_Test = a_aaa
    
    END
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do not do this. The scope of a trigger should ideally be limited to its table, but should never extend beyond its database. This is one of the most common ways that triggers are misused and is one of the reasons for their bad reputation.
    Use a technology like replication instead, or at least use the trigger to populate a staging table and then run a job the transfers data from your staging table to the target database. This is much safer and much more robust.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    Do not do this. The scope of a trigger should ideally be limited to its table, but should never extend beyond its database. This is one of the most common ways that triggers are misused and is one of the reasons for their bad reputation.

    +++1. Amen.


    What you are talking about doing should be (in my opinion) encapsualted in code in a "business object" that resides outside the database.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    Or you could upgrade to SQL Server 2005 and use the Service Broker.

  5. #5
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Triggers

    thank you very much for your replies.

    I think you maybe right on the overuse of the trigger in this instance. I will look into replication instead since my server is SQL Server 2k. If you have any favorite links to replication, docs I can read and/or the steps I can take, please feel free to foward the to me.

    Regards,

    alex

  6. #6
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    replication

    Hi,

    I can not use replication cause server a and b have different db's. Only one table in one has a corresponding column in the other that I want to use to update.

    Reviewing the original code above, is it correct and if not, then why is it wrong and what can i do to resolve this issue? I am looking for proper syntax.

    Regards,

    Alex.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Right of the bat, this is going to fail:
    Code:
    SELECT  @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
    ...since your select statement will likely return multiple rows that cannot be stored in a single variable.
    Triggers must be coded as set-based operations.
    Honestly, your SQL knowledge seems kind of slim (no shame in that), so you are going to need to do a lot of reading or find somebody who can help you code this. If you can't write fluent SQL you have no business messing around with triggers.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Triggers

    Excellent observation. For security reasons, I am guilty of providing you with incomplete and not fully formatted code. I apologize for this. Let me simplify it. My concern is not with the SQL as it is with the code regarding the firing of the trigger in one server to populate a table in another server.


    Code:
    CREATE TRIGGER mytable ON server_a 
       
    FOR  INSERT, UPDATE
    AS
    BEGIN
    	SET NOCOUNT ON;
        	DECLARE @PhoneALERT as VARCHAR(10)
    
    	SELECT  @PhoneALERT=phone1 from 
                 server_a.atable.thisphone 
                 where compCode = "abcde";
    
    
    	UPDATE server_b.tableb set ext = '111' 
                 WHERE @PhoneALERT= "2123659874";
    	
    END
    GO
    Regards,

    Alex.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not transfer data from between databases using triggers. Use a trigger to populate a staging table, and then use a scheduled SQL Agent job to transfer data from the staging table to the destination database.

    This statement will crash big time unless compCode is a unique key in server_a.atable.thisphone :
    Code:
    SELECT	@PhoneALERT=phone1 from
    	server_a.atable.thisphone
    where	compCode = "abcde";
    This statement is going to update either zero rows, or every damn row in the table, depending upon what @PhoneALERT was set to in your prior statement:
    Code:
    UPDATE	server_b.tableb set ext = '111' 
    WHERE	@PhoneALERT= "2123659874";
    Your trigger makes absolutely no use of the virtual "Inserted" or "Deleted" tables, does not account for multi-record operations, contains bad SQL, and is not appropriate for the task you are trying to accomplish. Again, I am not trying to offend you, but you either need to:
    A) Read a tutorial on SQL
    B) Get a professional DBA to help you with this
    C) Listen to the free advice people are giving you on this forum.
    There are good DBAs on this forum who will spend their time give you free advice (none of us knows why we do this), but we are honestly reluctant to spend our time helping people do things wrong.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    triggers

    Blindman,

    thanks for your comments. I will take your advise.

    Regards,


    Alex

Posting Permissions

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