Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Exclamation Unanswered: Update a record in another db using a trigger

    here is my trigger that i have right now the only problem is that it deletes the records before copying everything into the db i dont what do delete everything i just whant to catch the updated record and then update the other tables same record in the other db how would i do this:

    right now i have this


    CREATE TRIGGER test ON [dbo].[TEST123]
    AFTER INSERT, UPDATE, DELETE
    AS
    IF @@ROWCOUNT = 0
    RETURN

    IF (COLUMNS_UPDATED() & 2 = 2)
    DELETE FROM pubs..TEST123 WHERE test3 = '300'
    INSERT INTO pubs..TEST123
    SELECT * FROM TEST123 WHERE test3 = '300'
    UPDATE pubs..TEST123 SET test1 = 'X' WHERE test1 IS NULL AND test3 = '300'
    UPDATE pubs..TEST123 SET test2 = 'X' WHERE test2 IS NULL AND test3 = '300'
    UPDATE pubs..TEST123 SET test3 = 'X'

  2. #2
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    is there a way to detect a if a record changed and wich clolumns and then update that same record if it exist in the duplicate table in another db and if it dosent exist insert the new record in the other db ?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Trigger

    Here's a sample of a trigger we have after a field is updated. Hope it helps.

    CREATE TRIGGER [WECCMeasures1_Update] ON dbo.WECCMeasures
    After Update -- For Update
    AS
    declare @NewAC varchar(15), @OldAC varchar(15), @MP varchar(50), @BMT varchar(50), @MeasureID int, @MID int, @OPD datetime, @NPD datetime, @OCN int, @NCN int, @ORS varchar(50), @NRS varchar(50), @ORD datetime, @NRD datetime, @RA money
    If Update (AccountingCode) or update(CheckNumber) or update(DatePaid) or update(ReconcileStatus) or update(ReconcileDate)
    begin
    select @OldAC = AccountingCode from deleted
    select @NewAC = AccountingCode from inserted
    select @MeasureID = MeasureID from deleted
    select @OCN = CheckNumber from deleted
    select @NCN = CheckNumber from inserted
    select @OPD = DatePaid from Deleted
    select @NPD = DatePaid from inserted
    select @ORS = ReconcileStatus from deleted
    select @NRS = ReconcileStatus from inserted
    select @ORD = ReconcileDate from deleted
    select @NRD = ReconcileDate from inserted
    select @MP = MeasureProgram from deleted
    select @BMT = BillMeasureTo from deleted
    select @RA = RewardAmount from deleted

    if @OldAC <> "" or @OCN <> "" or @OPD <> "" or @ORS = "Cleared" or @ORS = "Void" or @ORD <> ""
    INSERT INTO WECCMeasuresChangeLog (MeasureID, MeasureProgram, BillMeasureTo, OldAccountingCode,NewAccountingCode, OldCheckNumber, NewCheckNumber, OldDatePaid, NewDatePaid, OldReconcileStatus, NewReconcileStatus, OldReconcileDate, NewReconcileDate, RewardAmount)
    Select @MeasureID, @MP, @BMT, @OldAC, @NewAC, @OCN, @NCN, @OPD, @NPD, @ORS, @NRS, @ORD, @NRD, @RA
    end

    We also then have another trigger which fires after a deletion:

    CREATE TRIGGER [WECCMeasures1_Delete] ON dbo.WECCMeasures
    FOR DELETE
    AS
    INSERT INTO WECCMeasuresDeleteLog ( MainID,MeasureID,ProjectID, RatingID, BillMeasureTo, MeasureProgram, MeasureType, MeasureDescription, MeasureKind, Qualified, RewardAmount, Quantity, MeasurePaidTo, BusinessID, MeasureStatus, PayMeasure, MeasurePaid, DatePaid, CheckNumber, AccountingCode, ReconcileStatus, KWSaved, KWHSaved, ThermSaved, ReconcileDate, EnteredBy, DateEntered )
    SELECT MainID, MeasureID, ProjectID, RatingID, BillMeasureTo, MeasureProgram, MeasureType, MeasureDescription, MeasureKind, Qualified, RewardAmount, Quantity, MeasurePaidTo, BusinessID, MeasureStatus, PayMeasure, MeasurePaid, DatePaid, CheckNumber, AccountingCode, ReconcileStatus, KWSaved, KWHSaved, ThermSaved, ReconcileDate, EnteredBy, DateEntered FROM deleted
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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