DEFINITIONS

Definitions More Info.
Definition ID2
TitlePOSTGRESQL
CategoryNOTES
Definitiontrigger for more than one table
Definition Description-- Let say we want to keep records log for delete from some tables, then we need to create first trigger function then, we create trigger on tables we want to keep logs.

--TRIGGER FUNCTION FOR DELETED RECORD LOG ON SOME TABLES
CREATE OR REPLACE FUNCTION "BACKUP"."BackupForDelete"()
RETURNS trigger
LANGUAGE plpgsql
AS $function$

BEGIN
-- #### D I K K A T ####
-- BU TRIGGER FUNCTION JENERIK YAZILMISTIR.
-- TABLOYA SPESIFIK ISLERI BURAYA YAMZAYIN...

IF TG_OP = 'DELETE' THEN

IF TG_TABLE_NAME = 'AccountingTransaction' THEN

INSERT INTO "BACKUP"."Deleted_AccountingTransaction" ("ID", "CompanyID", "AccountingRuleID", "AccountingTransactionTypeID", "DocumentNo", "Date", "Description")
VALUES (OLD."ID", OLD."CompanyID", OLD."AccountingRuleID", OLD."AccountingTransactionTypeID", OLD."DocumentNo", OLD."Date", OLD."Description");

ELSIF TG_TABLE_NAME = 'AccountingTransactionItem' THEN

INSERT INTO "BACKUP"."Deleted_AccountingTransactionItem" ("ID", "AccountingTransactionID", "ItemNo", "AccountID", "AccountReferenceID", "DebitAmount", "CreditAmount", "CurrencyID", "Description", "Quantity", "UnitOfMeasureID", "ExpenseTypeID", "ExpenseFunctionID", "ExpenseOrganizationID", "OriginalCurrencyID", "OriginalAmount", "DueDate")
VALUES (OLD."ID", OLD."AccountingTransactionID", OLD."ItemNo", OLD."AccountID", OLD."AccountReferenceID", OLD."DebitAmount", OLD."CreditAmount", OLD."CurrencyID", OLD."Description", OLD."Quantity", OLD."UnitOfMeasureID", OLD."ExpenseTypeID", OLD."ExpenseFunctionID", OLD."ExpenseOrganizationID", OLD."OriginalCurrencyID", OLD."OriginalAmount", OLD."DueDate");

ELSIF TG_TABLE_NAME = 'CashTransaction' THEN

INSERT INTO "BACKUP"."Deleted_CashTransaction" ("ID", "CompanyID", "OrganizationID", "CashTransactionTypeID", "DocumentNo", "OriginalDocumentNo", "Date", "BusinessPartnerID", "OppositeOrganizationID", "EmployeeID", "AccountingTransactionID", "EmploymentAgreementID", "Amount", "CurrencyID", "InvoiceID")
VALUES (OLD."ID", OLD."CompanyID", OLD."OrganizationID", OLD."CashTransactionTypeID", OLD."DocumentNo", OLD."OriginalDocumentNo", OLD."Date", OLD."BusinessPartnerID", OLD."OppositeOrganizationID", OLD."EmployeeID", OLD."AccountingTransactionID", OLD."EmploymentAgreementID", OLD."Amount", OLD."CurrencyID", OLD."InvoiceID");

ELSIF TG_TABLE_NAME = 'CashTransactionItem' THEN

INSERT INTO "BACKUP"."Deleted_CashTransactionItem" ("ID", "CompanyID", "CashTransactionID", "ItemNo", "Amount", "OriginalCurrencyID", "OriginalAmount", "OppositeCurrencyID", "OppositeAmount", "SpecialCurrencyRate", "CashboxID", "OppositeCashboxID", "BankAccountID", "OppositeBankAccountID", "BusinessPartnerID", "OppositeBusinessPartnerID", "InstalmentCount", "PaymentCommitmentID", "GiftChequeID", "ExpenseTypeID", "ExpenseFunctionID", "ExpenseOrganizationID", "ExpenseBusinessPartnerID", "Remarks", "Description", "BPAccountGroupID", "OppositeBPAccountGroupID", "CurrencyRateTypeID")
VALUES (OLD."ID", OLD."CompanyID", OLD."CashTransactionID", OLD."ItemNo", OLD."Amount", OLD."OriginalCurrencyID", OLD."OriginalAmount", OLD."OppositeCurrencyID", OLD."OppositeAmount", OLD."SpecialCurrencyRate", OLD."CashboxID", OLD."OppositeCashboxID", OLD."BankAccountID", OLD."OppositeBankAccountID", OLD."BusinessPartnerID", OLD."OppositeBusinessPartnerID", OLD."InstalmentCount", OLD."PaymentCommitmentID", OLD."GiftChequeID", OLD."ExpenseTypeID", OLD."ExpenseFunctionID", OLD."ExpenseOrganizationID", OLD."ExpenseBusinessPartnerID", OLD."Remarks", OLD."Description", OLD."BPAccountGroupID", OLD."OppositeBPAccountGroupID", OLD."CurrencyRateTypeID");

ELSIF TG_TABLE_NAME = 'InventoryTransaction' THEN

INSERT INTO "BACKUP"."Deleted_InventoryTransaction" ("ID", "CompanyID", "OrganizationID", "InventoryTransactionTypeID", "InventoryTransactionStatusID", "DocumentNo", "OriginalDocumentNo", "Date", "BusinessPartnerID", "BusinessPartnerLocationID", "InvoiceID", "OriginAddressID", "DestinationAddressID", "TaxOfficeID", "TaxNo", "PurchaseOrderID", "NextInventoryTransactionID", "NextInventoryTransactionTypeID", "NextBusinessPartnerID")
VALUES (OLD."ID", OLD."CompanyID", OLD."OrganizationID", OLD."InventoryTransactionTypeID", OLD."InventoryTransactionStatusID", OLD."DocumentNo", OLD."OriginalDocumentNo", OLD."Date", OLD."BusinessPartnerID", OLD."BusinessPartnerLocationID", OLD."InvoiceID", OLD."OriginAddressID", OLD."DestinationAddressID", OLD."TaxOfficeID", OLD."TaxNo", OLD."PurchaseOrderID", OLD."NextInventoryTransactionID", OLD."NextInventoryTransactionTypeID", OLD."NextBusinessPartnerID");

ELSIF TG_TABLE_NAME = 'InventoryTransactionItem' THEN

INSERT INTO "BACKUP"."Deleted_InventoryTransactionItem" ("ID", "CompanyID", "InventoryTransactionID", "ItemNo", "Description", "ProductID", "ProductColorID", "ProductDimensionID", "BOMItemID", "InventoryPackageID", "InventoryID", "Quantity", "UnitOfMeasureID", "PriceTypeID", "PromotionID", "UnitPrice", "CurrencyID", "Amount", "NetAmount", "SalesRepresentativeID", "BonusEarned", "BonusSpent", "TestRequestID", "ReturnReasonID", "ReturnOfInventoryTransactionItemID", "PurchaseOrderItemID", "ProductionOrderID", "ProductionID", "TaxDistributionID", "DefectionID")
VALUES (OLD."ID", OLD."CompanyID", OLD."InventoryTransactionID", OLD."ItemNo", OLD."Description", OLD."ProductID", OLD."ProductColorID", OLD."ProductDimensionID", OLD."BOMItemID", OLD."InventoryPackageID", OLD."InventoryID", OLD."Quantity", OLD."UnitOfMeasureID", OLD."PriceTypeID", OLD."PromotionID", OLD."UnitPrice", OLD."CurrencyID", OLD."Amount", OLD."NetAmount", OLD."SalesRepresentativeID", OLD."BonusEarned", OLD."BonusSpent", OLD."TestRequestID", OLD."ReturnReasonID", OLD."ReturnOfInventoryTransactionItemID", OLD."PurchaseOrderItemID", OLD."ProductionOrderID", OLD."ProductionID", OLD."TaxDistributionID", OLD."DefectionID");

ELSIF TG_TABLE_NAME = 'Invoice' THEN

-- BEFORE 2011-07-22
-- INSERT INTO "BACKUP"."Deleted_Invoice" ("ID", "CompanyID", "OrganizationID", "InvoiceTypeID", "InvoiceNo", "OriginalDocumentNo", "Date", "BusinessPartnerID", "BusinessPartnerName", "BusinessPartnerTitle", "BusinessPartnerAddressID", "TaxOfficeID", "TaxNo", "CurrencyID", "DiscountID", "AccountingTransactionID", "TaxOffice", "IsPrinted", "DueDate", "CurrencyRateTypeID", "SpecialCurrencyRate", "PaymentPlanID")
-- VALUES (OLD."ID", OLD."CompanyID", OLD."OrganizationID", OLD."InvoiceTypeID", OLD."InvoiceNo", OLD."OriginalDocumentNo", OLD."Date", OLD."BusinessPartnerID", OLD."BusinessPartnerName", OLD."BusinessPartnerTitle", OLD."BusinessPartnerAddressID", OLD."TaxOfficeID", OLD."TaxNo", OLD."CurrencyID", OLD."DiscountID", OLD."AccountingTransactionID", OLD."TaxOffice", OLD."IsPrinted", OLD."DueDate", OLD."CurrencyRateTypeID", OLD."SpecialCurrencyRate", OLD."PaymentPlanID");
INSERT INTO "BACKUP"."Deleted_Invoice"("ID", "CompanyID", "OrganizationID", "InvoiceTypeID", "InvoiceNo", "OriginalDocumentNo", "Date", "BusinessPartnerID", "BusinessPartnerName", "BusinessPartnerTitle", "BusinessPartnerAddressID", "TaxOfficeID", "TaxNo", "CurrencyID", "DiscountID", "AccountingTransactionID", "TaxOffice", "IsPrinted", "DueDate", "CurrencyRateTypeID", "SpecialCurrencyRate", "PaymentPlanID", "BusinessAgreementID", "StartDate", "EndDate", "CustomsDeclarationID", "SurchargeID", "CancelDate", "AppliedExchangeRate")
VALUES (OLD."ID", OLD."CompanyID", OLD."OrganizationID", OLD."InvoiceTypeID", OLD."InvoiceNo", OLD."OriginalDocumentNo", OLD."Date", OLD."BusinessPartnerID", OLD."BusinessPartnerName", OLD."BusinessPartnerTitle", OLD."BusinessPartnerAddressID", OLD."TaxOfficeID", OLD."TaxNo", OLD."CurrencyID", OLD."DiscountID", OLD."AccountingTransactionID", OLD."TaxOffice", OLD."IsPrinted", OLD."DueDate", OLD."CurrencyRateTypeID", OLD."SpecialCurrencyRate", OLD."PaymentPlanID", OLD."BusinessAgreementID", OLD."StartDate", OLD."EndDate", OLD."CustomsDeclarationID", OLD."SurchargeID", OLD."CancelDate", OLD."AppliedExchangeRate");

ELSIF TG_TABLE_NAME = 'InvoiceItem' THEN

-- BEFORE 2011-07-22
-- INSERT INTO "BACKUP"."Deleted_InvoiceItem" ("ID", "CompanyID", "InvoiceID", "ItemNo", "InventoryTransactionItemID", "Description", "Quantity", "PriceTypeID", "UnitOfMeasureID", "UnitPrice", "Amount", "NetAmount", "OriginalCurrencyID", "OriginalUnitPrice", "OriginalAmount", "OriginalNetAmount", "TaxDistributionID", "ExpenseOrganizationID", "ExpenseTypeID", "ExpenseFunctionID", "ExpenseAccountID", "Distributable", "ExpenseDistributionListID", "ProcurementTypeID", "AccountID", "AccountReferenceID")
-- VALUES (OLD."ID", OLD."CompanyID", OLD."InvoiceID", OLD."ItemNo", OLD."InventoryTransactionItemID", OLD."Description", OLD."Quantity", OLD."PriceTypeID", OLD."UnitOfMeasureID", OLD."UnitPrice", OLD."Amount", OLD."NetAmount", OLD."OriginalCurrencyID", OLD."OriginalUnitPrice", OLD."OriginalAmount", OLD."OriginalNetAmount", OLD."TaxDistributionID", OLD."ExpenseOrganizationID", OLD."ExpenseTypeID", OLD."ExpenseFunctionID", OLD."ExpenseAccountID", OLD."Distributable", OLD."ExpenseDistributionListID", OLD."ProcurementTypeID", OLD."AccountID", OLD."AccountReferenceID");
INSERT INTO "BACKUP"."Deleted_InvoiceItem"("ID", "CompanyID", "InvoiceID", "ItemNo", "InventoryTransactionItemID", "Description", "Quantity", "PriceTypeID", "UnitOfMeasureID", "UnitPrice", "Amount", "NetAmount", "OriginalCurrencyID", "OriginalUnitPrice", "OriginalAmount", "OriginalNetAmount", "TaxDistributionID", "ExpenseOrganizationID", "ExpenseTypeID", "ExpenseFunctionID", "ExpenseAccountID", "Distributable", "ExpenseDistributionListID", "ProcurementTypeID", "AccountID", "AccountReferenceID", "CustomsDeclarationID", "ReflectedBusinessPartnerID", "ReflectedRatio", "RelatedInvoiceItemID", "IncomeTypeID", "IncomeAccountID", "IncomeOrganizationID")
VALUES (OLD."ID", OLD."CompanyID", OLD."InvoiceID", OLD."ItemNo", OLD."InventoryTransactionItemID", OLD."Description", OLD."Quantity", OLD."PriceTypeID", OLD."UnitOfMeasureID", OLD."UnitPrice", OLD."Amount", OLD."NetAmount", OLD."OriginalCurrencyID", OLD."OriginalUnitPrice", OLD."OriginalAmount", OLD."OriginalNetAmount", OLD."TaxDistributionID", OLD."ExpenseOrganizationID", OLD."ExpenseTypeID", OLD."ExpenseFunctionID", OLD."ExpenseAccountID", OLD."Distributable", OLD."ExpenseDistributionListID", OLD."ProcurementTypeID", OLD."AccountID", OLD."AccountReferenceID", OLD."CustomsDeclarationID", OLD."ReflectedBusinessPartnerID", OLD."ReflectedRatio", OLD."RelatedInvoiceItemID", OLD."IncomeTypeID", OLD."IncomeAccountID", OLD."IncomeOrganizationID");

ELSIF TG_TABLE_NAME = 'Order' THEN

INSERT INTO "BACKUP"."Deleted_Order"("ID", "CompanyID", "OrganizationID", "OrderTypeID", "OrderNo", "Date", "BusinessPartnerID", "BusinessPartnerLocationID", "BusinessPartnerOrderNo", "IsSampleOrder", "OrderStatusID", "BusinessPartnerContactPersonID", "DeliveryTypeID", "DeliveryLocation", "DeliveryDate", "CustomsProcedureID", "ApprovalSetInstanceID", "PaymentPlanID", "PaymentPlanDiscountRatio", "AlternatePaymentPlanID", "AlternatePaymentPlanDiscountRatio", "DiscountID")
VALUES (OLD."ID", OLD."CompanyID", OLD."OrganizationID", OLD."OrderTypeID", OLD."OrderNo", OLD."Date", OLD."BusinessPartnerID", OLD."BusinessPartnerLocationID", OLD."BusinessPartnerOrderNo", OLD."IsSampleOrder", OLD."OrderStatusID", OLD."BusinessPartnerContactPersonID", OLD."DeliveryTypeID", OLD."DeliveryLocation", OLD."DeliveryDate", OLD."CustomsProcedureID", OLD."ApprovalSetInstanceID", OLD."PaymentPlanID", OLD."PaymentPlanDiscountRatio", OLD."AlternatePaymentPlanID", OLD."AlternatePaymentPlanDiscountRatio", OLD."DiscountID");

ELSIF TG_TABLE_NAME = 'OrderItem' THEN

INSERT INTO "BACKUP"."Deleted_OrderItem"("ID", "CompanyID", "OrderID", "ItemNo", "ProductID", "ProductColorID", "ProductConfigurationID", "Quantity", "QuantityDistributionID", "DeliveryDate", "UnitPrice", "PriceTypeID", "RelatedOrderItemID", "CurrencyID", "DiscountRatio", "TaxDistributionID", "AppliedExchangeRate", "Description", "ExpenseOrganizationID", "ExpenseTypeID", "ExpenseFunctionID")
VALUES (OLD."ID", OLD."CompanyID", OLD."OrderID", OLD."ItemNo", OLD."ProductID", OLD."ProductColorID", OLD."ProductConfigurationID", OLD."Quantity", OLD."QuantityDistributionID", OLD."DeliveryDate", OLD."UnitPrice", OLD."PriceTypeID", OLD."RelatedOrderItemID", OLD."CurrencyID", OLD."DiscountRatio", OLD."TaxDistributionID", OLD."AppliedExchangeRate", OLD."Description", OLD."ExpenseOrganizationID", OLD."ExpenseTypeID", OLD."ExpenseFunctionID");

ELSIF TG_TABLE_NAME = 'ProductCombination' THEN

INSERT INTO "BACKUP"."Deleted_ProductCombination"("ID", "CompanyGroupID", "OrganizationID", "Code", "Name", "DateCreated", "Year", "Season")
VALUES (OLD."ID", OLD."CompanyGroupID", OLD."OrganizationID", OLD."Code", OLD."Name", OLD."DateCreated", OLD."Year", OLD."Season");

ELSIF TG_TABLE_NAME = 'ProductCombinationItem' THEN

INSERT INTO "BACKUP"."Deleted_ProductCombinationItem"("ID", "ProductCombinationID", "ItemNo", "ProductID", "ProductColorID")
VALUES (OLD."ID", OLD."ProductCombinationID", OLD."ItemNo", OLD."ProductID", OLD."ProductColorID");

ELSIF TG_TABLE_NAME = 'TestRequest' THEN

INSERT INTO "BACKUP"."Deleted_TestRequest"("ID", "CompanyID", "BPID", "BPName", "OrganizationID", "SalesRepresentationID", "TransactionNo", "TransactionItemNo", "TransactionDate", "PhoneNumber", "No", "Date", "Description", "TestRequestReasonID", "InventoryTrackingNo", "InventoryTransactionItemID", "DeliveryDate", "InventoryID", "ContactBPName", "ContactPhoneNumber", "ContactAddress", "DocumentBPCode", "SalesRepresentativeName", "DocumentBPName")
VALUES (OLD."ID", OLD."CompanyID", OLD."BPID", OLD."BPName", OLD."OrganizationID", OLD."SalesRepresentationID", OLD."TransactionNo", OLD."TransactionItemNo", OLD."TransactionDate", OLD."PhoneNumber", OLD."No", OLD."Date", OLD."Description", OLD."TestRequestReasonID", OLD."InventoryTrackingNo", OLD."InventoryTransactionItemID", OLD."DeliveryDate", OLD."InventoryID", OLD."ContactBPName", OLD."ContactPhoneNumber", OLD."ContactAddress", OLD."DocumentBPCode", OLD."SalesRepresentativeName", OLD."DocumentBPName");

ELSIF TG_TABLE_NAME = 'Test' THEN

INSERT INTO "BACKUP"."Deleted_Test"("ID", "CompanyID", "TestRequestID", "TesterEmployeeID", "Description", "InventoryReturnDate", "Date", "TestResultID", "InventoryTrackingNo", "IsValidated", "ValidatorEmployeeID", "TestResultItemID")
VALUES (OLD."ID", OLD."CompanyID", OLD."TestRequestID", OLD."TesterEmployeeID", OLD."Description", OLD."InventoryReturnDate", OLD."Date", OLD."TestResultID", OLD."InventoryTrackingNo", OLD."IsValidated", OLD."ValidatorEmployeeID", OLD."TestResultItemID");

ELSIF TG_TABLE_NAME = 'Address' THEN

INSERT INTO "BACKUP"."Deleted_Address"("ID", "CountryID", "CityID", "BoroughID", "AddressLine1", "AddressLine2", "PostalCode", "DistrictID")
VALUES (OLD."ID", OLD."CountryID", OLD."CityID", OLD."BoroughID", OLD."AddressLine1", OLD."AddressLine2", OLD."PostalCode", OLD."DistrictID");

ELSIF TG_TABLE_NAME = 'E_Archive' THEN

INSERT INTO "BACKUP"."Deleted_E_Archive"("ID", "InvoiceID", "Invoice", "InvoiceUUID", "IsProcessed", "ProcessedDate", "IsElectronicSale",
"IsCancelled", "CancellationDate", "CancelledByUserName","DeletedTime")
VALUES (OLD."ID", OLD."InvoiceID", OLD."Invoice", OLD."InvoiceUUID", OLD."IsProcessed", OLD."ProcessedDate", OLD."IsElectronicSale",
OLD."IsCancelled", OLD."CancellationDate", OLD."CancelledByUserName", now());

ELSIF TG_TABLE_NAME = 'PLM_ProductImport' THEN

INSERT INTO "EDI"."PLM_ProductImport_Deleted" SELECT OLD.*;

ELSIF TG_TABLE_NAME = 'PLM_ProductColorImport' THEN

INSERT INTO "EDI"."PLM_ProductColorImport_Deleted" SELECT OLD.*;

ELSIF TG_TABLE_NAME = 'PLM_ProductColorCostImport' THEN

INSERT INTO "EDI"."PLM_ProductColorCostImport_Deleted" SELECT OLD.*;

ELSIF TG_TABLE_NAME = 'PLM_ProductDimensionImport' THEN

INSERT INTO "EDI"."PLM_ProductDimensionImport_Deleted" SELECT OLD.*;

ELSIF TG_TABLE_NAME = 'PLM_ProductBOMImport' THEN

INSERT INTO "EDI"."PLM_ProductBOMImport_Deleted" SELECT OLD.*;

END IF;

END IF;

RETURN NULL;

END;
$function$;

-- THEN WE NEED TO CREATE TRIGGER ON THOSE TABLES WHICH WE CREATED AT TRIGGER FUCNTION.

-- TRIGGER FOR ADDRESS
CREATE
TRIGGER "Address_BackupForDelete" AFTER DELETE
ON
"Address" FOR EACH ROW EXECUTE PROCEDURE "BACKUP"."BackupForDelete"();

-- TRIGGER FOR ORDER TABLE AND SO ON....
CREATE
TRIGGER "Order_BackupForDelete" AFTER DELETE
ON
"Order" FOR EACH ROW EXECUTE PROCEDURE "BACKUP"."BackupForDelete"();
RecordBycunay
Record Date10-09-2018 10:15:48
Düzenle
Kopyala
Sil