I have two databases with identical tables called comments. I set up a trigger on each that will insert any rows added to that table to the table in the other database. This works great. Whichever database I am logged into when I create a new comment it is also inserted in the other table. The problem is when I try to modify a comment. I am getting a key violation because the insert trigger is executing. I am wondering how I keep this trigger from executing. I also need to add another one that will update the other table when one is modified.
Perhaps you need an UPDATE trigger when you update (and also a delete trigger when you delete)?
Is there a reason to keep two copies of the data? You can query data from a table residing in one database from both databases. If you can control the client code so they always go through stored procs, it would be easier and more manageable to update/insert data.
An INSERT trigger shouldn't fire at all when you do updates(edit: corrected). And any UPDATE you do shouldn't be restricted by an INSERT trigger (unless it I guess:(1) affects exactly the same rows (it shouldn't -- you couldn't really be UPDATEing a row before it was inserted); (2) you have nonclus indexes and the combination of INSERT / UPDATE is causing some type of locking/deadlocking issue with the indexes.)
Would need to see more details of the trigger and how you're copying the rows. Are you using identity values? Are you using SET IDENTITY_INSERT ON? And so on.
There are four indexes on the table. The unique index causing the problem is non-clustered. No Identity values. The insert and update is not occurring at the same time.
> CREATETRIGGER [dbo].[DB2_comment_update]
> ON [dbo].[cc_comments]
> AFTER UPDATE
> AS
> BEGIN
>
> UPDATE DB2.dbo.cc_comments
> SET comments = lres.comments
> FROM (select top 1 comments, comment_id from DB1.dbo.cc_comments order by updated_comment_date desc) lres
> WHERE cc_comments.comment_id = lres.comment_id
>
> END
CREATE TABLE [dbo].[comments](
[comment_id] [int] NOT NULL,
[row_num] [int] NOT NULL,
[customer_code] [varchar](20) NOT NULL,
[user_name] [varchar](20) NOT NULL,
[comment_date] [datetime] NOT NULL,
[comments] [varchar](255) NOT NULL,
[log_type] [tinyint] NULL,
[doc_ctrl_num] [varchar](16) NULL,
[updated_comment_date] [datetime] NULL,
[updated_user_name] [varchar](20) NULL,
[from_alerts] [smallint] NULL
) ON [PRIMARY]
That appears to be using the [updated_comment_date] column in DB1.dbo.cc_comments to find the most recent date and then transferring that comment to the matching row (based on comment_id) in DB2.dbo.cc_comments
Triggers should be written to handle multiple rows, this will fail if more than one row is updated.
This trigger is an AFTER UPDATE on cc_comments table in the current database (i.e. where the script is run). Personally I would not explicitly name the database in DB1.dbo.cc_comments because if this script / database is moved elsewhere the update will be performed based on the most recent row in DB1, not the "current" database.
I suggest you build your Trigger based on the pseudo tables inserted and deleted, rather than the "most recent update date" updated_comment_date column
Kristen,
The application opens only one record at a time that can then be updated which is why I chose to select just the top 1.
I am trying to use the inserted table but after removing all other (insert) triggers I am trying to do just the update and it will not execute. This was happening when using the top 1 statement also. If I execute that statement it works but not from the trigger.
Very dangerous strategy. You should build your triggers to work with recordsets that have more than one row, otherwise, sooner or later, that will happen.
Or put a RAISERROR in the Trigger if more than one row is encountered.
You need to use INSERTED table. Post the code, which didn't work using INSERTED table, and we'll take a look and make suggestions.
What about the explicit reference to [DB1] database?
Might be that the the new record is not yet visible (within the trigger). Seems unlikely, but that sort of thing can happen with read Committed Snapshot isolation, for example. If that is the case, using INSERTED rather than referencing the table itself should fix the problem.
Thanks Kristen. Just got it working using the inserted table.
I am not sure what the issue was before. I deleted everything and started from scratch. I am deleting everything that matches the inserted table then inserting everything in the inserted table.
