In this blog, we will see how to delete DB trigger in SLT ( SAP LT Replication server)
There are different possibilities on how to delete a trigger in the system:
1. using functionalities from SAP Landscape Transformation (requires authorization delivered with role SAP_IUUC_USER)
2. using database-specific SQL commands (requires access to database console)
1: FROM SAP GUI Level.
Run Tcode SE38 --> then program name : IUUC_DELETE_TRIGGERS_REMOTE
There are different possibilities on how to delete a trigger in the system:
1. using functionalities from SAP Landscape Transformation (requires authorization delivered with role SAP_IUUC_USER)
2. using database-specific SQL commands (requires access to database console)
1: FROM SAP GUI Level.
Run Tcode SE38 --> then program name : IUUC_DELETE_TRIGGERS_REMOTE
Click on Execute
Note: we need to enter table name from we want to delete Triggers. Keep the default selection.
Then click on execute
Note to understand the meaning for two select options refers below link.
Click on Yes
we will get a log screen.
2: 2. using database-specific SQL commands (requires access to database console)
We can also delete the DB trigger from the Source system by running the SQL statement.
If the tool based deletion does not work properly you can delete the triggers directly on the database using database-specific SQL commands. First, you need to derive the technical name of the trigger. The easiest way is to use program IUUC_LIST_TRIGGERS to get the name of the trigger(s) for certain tables. Depending on the database system, up to four triggers are created for a table per data replication scenario of SAP Landscape Transformation. The numeric characters of the logging table are used as a prefix (for more details see the database-specific section below). If DB triggers are created with a current version of DMIS, the DB trigger use namespace /1LT/ as the prefix. In older DMIS version namespace /1CADMC/ or no prefix was used in the trigger name.
ORA - Oracle
In case of DMIS 2011 support packages lower than support package 09, one trigger is created to handle inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS for the delta trigger and /1LT/nnnnnnnnFRI for the freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
Starting from DMIS 2011 support package 09, we have specific DB triggers for inserts, updates, and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta triggers (recording triggers); and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297FRU).
DB2 - DB2 for z/OS
For each DB operation, a specific trigger is created. For updates, we even need two triggers to distinguish key and non-key updates, in case of change recording. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1, /1LT/nnnnnnnnUPD2 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER trigger_name RESTRICT
Example: DROP TRIGGER /1LT/00000297INS RESTRICT
DB4 - DB2 for AS/400
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). To drop the trigger also the database library is required. The naming convention for the library is R3<SID>DATA. A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER db_library/"trigger_name"
Example: DROP TRIGGER R3LS8DATA/"/1LT/00000297INS"
DB6 - DB2 for Unix, Windows, etc.
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
MSS - Microsoft SQL Server
A separate delta trigger is created for inserts, updates and deletes.
In case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we also have three freeze triggers, the same way as for all other DB systems.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
If there is a high load on a table, the drop trigger statement cannot be executed and this might lead to a lock situation on the database which is not resolved automatically.
Therefore we recommend to set a lock timeout to define that the drop statement should be stopped after a certain time:
SET LOCK_TIMEOUT nnnn (where nnnn is number in milli seconds)
After executing the drop statement the lock timeout should be reset to the default value:
SET LOCK_TIMEOUT -1
ADA - SAP DB (MAX DB)
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
ASE - SAP ASE
A separate delta trigger is created for inserts, updates and deletes.
In case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we also have three freeze triggers, the same way as for all other DB systems.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
HDB - SAP HANA
A separate delta trigger is created for inserts, updates, and deletes.
Starting from SAP HANA DB revision 91 and DMIS 2011 support package 09, we are using, for change recording, 2 update triggers to distinguish key field updates from updates of non-key-fields only.
In the case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we have three freeze triggers.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1, /1LT/nnnnnnnnUPD2 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
Note: if you are facing error then run a statement with a schema name.
In case of DMIS 2011 support packages lower than support package 09, one trigger is created to handle inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS for the delta trigger and /1LT/nnnnnnnnFRI for the freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
Starting from DMIS 2011 support package 09, we have specific DB triggers for inserts, updates, and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta triggers (recording triggers); and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297FRU).
DB2 - DB2 for z/OS
For each DB operation, a specific trigger is created. For updates, we even need two triggers to distinguish key and non-key updates, in case of change recording. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1, /1LT/nnnnnnnnUPD2 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER trigger_name RESTRICT
Example: DROP TRIGGER /1LT/00000297INS RESTRICT
DB4 - DB2 for AS/400
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). To drop the trigger also the database library is required. The naming convention for the library is R3<SID>DATA. A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER db_library/"trigger_name"
Example: DROP TRIGGER R3LS8DATA/"/1LT/00000297INS"
DB6 - DB2 for Unix, Windows, etc.
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
MSS - Microsoft SQL Server
A separate delta trigger is created for inserts, updates and deletes.
In case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we also have three freeze triggers, the same way as for all other DB systems.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
If there is a high load on a table, the drop trigger statement cannot be executed and this might lead to a lock situation on the database which is not resolved automatically.
Therefore we recommend to set a lock timeout to define that the drop statement should be stopped after a certain time:
SET LOCK_TIMEOUT nnnn (where nnnn is number in milli seconds)
After executing the drop statement the lock timeout should be reset to the default value:
SET LOCK_TIMEOUT -1
ADA - SAP DB (MAX DB)
A separate trigger is created for inserts, updates and deletes. The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRU, /1LT/nnnnnnnnFRD for freeze trigger (example: /1LT/00000297INS). A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
ASE - SAP ASE
A separate delta trigger is created for inserts, updates and deletes.
In case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we also have three freeze triggers, the same way as for all other DB systems.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"
HDB - SAP HANA
A separate delta trigger is created for inserts, updates, and deletes.
Starting from SAP HANA DB revision 91 and DMIS 2011 support package 09, we are using, for change recording, 2 update triggers to distinguish key field updates from updates of non-key-fields only.
In the case of DMIS 2011 support packages lower than support package 09, for table freeze, only a single trigger is created.
As of support package 09, we have three freeze triggers.
The naming convention is as follows: /1LT/nnnnnnnnINS, /1LT/nnnnnnnnUPD1, /1LT/nnnnnnnnUPD2 and /1LT/nnnnnnnnDEL for delta trigger and /1LT/nnnnnnnnFRI, /1LT/nnnnnnnnFRD and /1LT/nnnnnnnnFRU for the freeze trigger (example: /1LT/00000297INS).
A trigger can be deleted on database level with the following SQL command:
DROP TRIGGER "trigger_name"
Example: DROP TRIGGER "/1LT/00000297INS"