Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: Trigger Problems...

    Hi Everyone,

    Im currently trying to create a trigger that will, when a row is inserted, also copy that row into another table in another db...im sorry if this has been posted before, but i only am only learning about triggers today, and dont really know what to search for...

    here is the code to my trigger...

    Code:
    ALTER TRIGGER dbo.UpdateCrmDetails 
       ON  dbPortal.dbo.mentors
       AFTER INSERT
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for trigger here
    
    	DECLARE @contact_given VARCHAR(50), 
    			@contact_surname VARCHAR(50),
    			@contact_email_address VARCHAR(100), 
    			@contact_postal_address VARCHAR(100),
    			@contact_postal_city VARCHAR(50),
    			@contact_postal_state VARCHAR(3),
    			@contact_postal_code INT,
    			@contact_phone VARCHAR(15),
    			@contact_mobile VARCHAR(15),
    			@contact_date_modified DATETIME
    
    			SELECT @contact_given FROM Inserted.FNAME
    			SELECT @contact_surname FROM Inserted.LNAME
    			SELECT @contact_email_address FROM Inserted.EmailAdd
    			SELECT @contact_postal_address FROM Inserted.PAdd
    			SELECT @contact_postal_city FROM Inserted.PTown
    			SELECT @contact_postal_state FROM Inserted.PState
    			SELECT @contact_postal_code FROM Inserted.PPCode
    			SELECT @contact_phone FROM Inserted.Ph_No
    			SELECT @contact_mobile FROM Inserted.Mobile_No
    			SET @contact_date_modified = '4/05/2007 12:00:00'
    
    
    	INSERT INTO braceCRM.dbo.tbl_contacts (
    
    	contact_given, contact_surname, 
    	contact_email_address, contact_postal_address, contact_postal_city, contact_postal_state, 
    	contact_postal_code, contact_phone, contact_mobile, contact_date_modified
    
    	) VALUES (
    	
    	@contact_given, @contact_surname, 
    	@contact_email_address, @contact_postal_address, @contact_postal_city, @contact_postal_state, 
    	@contact_postal_code, @contact_phone, @contact_mobile, @contact_date_modified
    	
    	)
    
    END
    GO
    I am getting the error mesage of:
    No row was updated.

    The data in row 20 was not committed.
    Error Source: .Net SqlClient Data Provider.
    Error Message: Invalid object name 'Inserted.FNAME'.
    Now, as i understand from my reading, the Inserted table is a temporary table that spans the life of the trigger and holds the row that was to be inserted...

    im also filling in all the required fields, so its not that...

    any help would be greatly appreciated.

    Cheers, Justin

  2. #2
    Join Date
    Aug 2005
    Posts
    55
    Hi,

    I worked it out, i was declaring my declarations wrong.

    Cheers, Justin

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    NO NO NO NO NO NO NO NO!
    I must have told you a thousand times, do NOT use triggers to transfer data to another database!
    OK, maybe not YOU, but a thousand other people, anyway.
    The scope of a trigger should be limited to the table on which it is implemented, or at most the database in which it resides. Creating a trigger that affects or relies upon anything outside the database is a recipe for disaster.
    The proper method of handling this is to write your trigger to populate a staging table within the same database, and then have a scheduled job transfer the data from the staging table to the target database.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    or, write a CLR trigger that calls Environment.Exit() and call it a day.

Posting Permissions

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