Record trackings procedure

01-procedure.sql

CREATE OR REPLACE PROCEDURE track_record (
  p_record_id IN NUMBER,
  p_record_type IN VARCHAR2,
  p_is_deleted IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
  IF p_is_deleted THEN
    UPDATE record_trackings
    SET record_deleted_at = SYSTIMESTAMP
    WHERE record_id = p_record_id AND record_type = p_record_type;
  ELSE
    MERGE INTO record_trackings rt
    USING (SELECT p_record_id AS record_id,
                  p_record_type AS record_type
            FROM dual) src
    ON (rt.record_id = src.record_id AND rt.record_type = src.record_type)
    WHEN MATCHED THEN
        UPDATE SET rt.record_sync_step = 0,
                   rt.record_updated_at = SYSTIMESTAMP,
                   rt.updated_at = SYSTIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT (
          id,
          created_at,
          updated_at,
          record_id,
          record_type,
          record_sync_step,
          record_created_at,
          record_updated_at)
        VALUES (
          record_trackings_seq.nextval,
          SYSTIMESTAMP,
          SYSTIMESTAMP,
          src.record_id,
          src.record_type,
          0,
          SYSTIMESTAMP,
          SYSTIMESTAMP);
  END IF;
END;

02-trigger.sql

CREATE OR REPLACE TRIGGER trg_motivo_assistencia_trk
AFTER INSERT OR UPDATE OR DELETE ON tb_motivo_assistencia
FOR EACH ROW
BEGIN
  track_record(COALESCE(:NEW.cod_motivo_assistencia, :OLD.cod_motivo_assistencia), 'CGS::SupportRequestReason', DELETING);
END;

03-testing.rb

srr = CGS::SupportRequestReason.create!(cod_motivo_assistencia: 99, desc_motivo_assistencia: 'Teste Diego')
# D, [2023-09-21T23:43:34.558888 #1075968] DEBUG -- :   CGS::SupportRequestReason Create (132.0ms)  INSERT INTO "TB_MOTIVO_ASSISTENCIA" ("COD_MOTIVO_ASSISTENCIA", "DESC_MOTIVO_ASSISTENCIA") VALUES (:a1, :a2)  [["cod_motivo_assistencia", 99], ["desc_motivo_assistencia", "Teste Diego"]]

CGS::RecordTracking.last
# D, [2023-09-21T23:43:39.849819 #1075968] DEBUG -- :   CGS::RecordTracking Load (141.8ms)  SELECT * FROM (SELECT "RECORD_TRACKINGS".* FROM "RECORD_TRACKINGS" ORDER BY "RECORD_TRACKINGS"."ID" DESC ) WHERE ROWNUM <= :a1  [["LIMIT", 1]]
# => 
# #<CGS::RecordTracking:0x000055daa8ad2ab8
#  id: 11,
#  record_sync_step: 0,
#  record_created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_updated_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_deleted_at: nil,
#  record_sync_error_message: nil,
#  record_type: "CGS::SupportRequestReason",
#  record_id: 99,
#  created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  updated_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00>

CGS::RecordTracking.last.update! record_sync_step: 1
CGS::RecordTracking.last
# D, [2023-09-21T23:44:25.702089 #1075968] DEBUG -- :   CGS::RecordTracking Load (132.2ms)  SELECT * FROM (SELECT "RECORD_TRACKINGS".* FROM "RECORD_TRACKINGS" ORDER BY "RECORD_TRACKINGS"."ID" DESC ) WHERE ROWNUM <= :a1  [["LIMIT", 1]]
# => 
# #<CGS::RecordTracking:0x000055daa2d776e8
#  id: 11,
#  record_sync_step: 1,
#  record_created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_updated_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_deleted_at: nil,
#  record_sync_error_message: nil,
#  record_type: "CGS::SupportRequestReason",
#  record_id: 99,
#  created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  updated_at: Thu, 21 Sep 2023 23:44:19.885102000 -03 -03:00>

srr.update!(desc_motivo_assistencia: 'Teste Diego 2')
CGS::RecordTracking.last
# D, [2023-09-21T23:44:37.154591 #1075968] DEBUG -- :   CGS::RecordTracking Load (131.9ms)  SELECT * FROM (SELECT "RECORD_TRACKINGS".* FROM "RECORD_TRACKINGS" ORDER BY "RECORD_TRACKINGS"."ID" DESC ) WHERE ROWNUM <= :a1  [["LIMIT", 1]]
# => 
# #<CGS::RecordTracking:0x000055daa83c8a10
#  id: 11,
#  record_sync_step: 0,
#  record_created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_updated_at: Thu, 21 Sep 2023 20:40:42.323000000 -03 -03:00,
#  record_deleted_at: nil,
#  record_sync_error_message: nil,
#  record_type: "CGS::SupportRequestReason",
#  record_id: 99,
#  created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  updated_at: Thu, 21 Sep 2023 20:40:42.323000000 -03 -03:00>

srr.destroy
# D, [2023-09-21T23:46:29.967933 #1075968] DEBUG -- :   CGS::SupportRequestReason Destroy (131.8ms)  DELETE FROM "TB_MOTIVO_ASSISTENCIA" WHERE "TB_MOTIVO_ASSISTENCIA"."COD_MOTIVO_ASSISTENCIA" = :a1  [["cod_motivo_assistencia", 99]]

CGS::RecordTracking.last
# D, [2023-09-21T23:46:58.426947 #1075968] DEBUG -- :   CGS::RecordTracking Load (135.2ms)  SELECT * FROM (SELECT "RECORD_TRACKINGS".* FROM "RECORD_TRACKINGS" ORDER BY "RECORD_TRACKINGS"."ID" DESC ) WHERE ROWNUM <= :a1  [["LIMIT", 1]]
# => 
# #<CGS::RecordTracking:0x000055daa87a3b80
#  id: 11,
#  record_sync_step: 0,
#  record_created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  record_updated_at: Thu, 21 Sep 2023 20:40:42.323000000 -03 -03:00,
#  record_deleted_at: Thu, 21 Sep 2023 20:42:40.535000000 -03 -03:00,
#  record_sync_error_message: nil,
#  record_type: "CGS::SupportRequestReason",
#  record_id: 99,
#  created_at: Thu, 21 Sep 2023 20:39:45.128000000 -03 -03:00,
#  updated_at: Thu, 21 Sep 2023 20:40:42.323000000 -03 -03:00>