Notepad++ productivity tips with SQL trigger code example

One of the traits of good developers is that being lazy and this laziness leads to finding productive ways to perform a task. Recently, I had to create a number of triggers to a few existing tables. It is quite a repetitive task, hence I decided to use notepad++ regex feature with find/replace to make a bit more interesting and productive. Here is what I had to do

From existing DDL

CREATE TABLE bus_rule_audit(
    id                           NUMBER(30)     NOT NULL,
    code                         VARCHAR2(50)   NOT NULL,
    rule_entry_id                NUMBER(30)     NOT NULL,
    name                         VARCHAR2(100)  NOT NULL,
    effective                    date           NULL,
    expires                      date           NULL,
    created_by                   VARCHAR2(30)   NOT NULL,
    created_date                 TIMESTAMP      NOT NULL,
    updated_by                   VARCHAR2(30)   NOT NULL,
    updated_date                 TIMESTAMP      NOT NULL,
);

To, following INSERT statement in the trigger

CREATE OR REPLACE TRIGGER bus_rule_trig

AFTER INSERT ON bus_rule

FOR EACH ROW
 
  BEGIN

      INSERT INTO bus_rule_audit (ID,CODE,RULE_ENTRY_ID,NAME,EFFECTIVE,EXPIRES,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,CRUD_OPERATION) 
        VALUES (:NEW.ID, :NEW.CODE,:NEW.RULE_ENTRY_ID,:NEW.NAME,:NEW.EFFECTIVE,:NEW.EXPIRES,:NEW.CREATED_BY,:NEW.CREATED_DATE,:NEW.UPDATED_BY,:NEW.UPDATED_DATE,  crud_operation);

       
  END;

Here are the steps to perform the transformation with minimal or no typing involved.

Step 1: Copy column names in the DDL to the text pad and remove the data type and NOT NULL column values to retain only the column names. Place the cursor LHS of "NUMBER(30)" and press Alt + Shift + highlight with the mouse and then press delete to delete the highlighted columns.

Step 2: Click on the non-printing symbols button to see the CR/LF and replace them with "" as shown below. Use CTRL + F to pop up the Find/Replace, and select the Replace tab.

Find: \r\n
Replace:

In Extended mode as shown below.

Step 3: Select the "Regular expression" search mode, and perform

Find: [\s]*
Replace: ,

You need to delete the first and last ",". This result can be now copied and pasted for INSERT INTO ( ....) for the column names.

Next, for the values ...

Step 4: The following regex is used to prefix each column name with ":NEW."

Find: ([^,]*),
Replace: :NEW.\1,

You should now have the values as

:NEW.id,:NEW.code,:NEW.rule_entry_id,:NEW.name,:NEW.effective,:NEW.expires,:NEW.created_by,:NEW.created_date,:NEW.updated_by,updated_date.

You can even convert case with Edit --> Convert Case to


You may also like other Notepad++ and Excel tutorials on developer productivity

• Software developer productivity tip with Notepad++ to construct SQL clause or any other data conversion
• Notepad++ with power of regex as a productivity tool for developers
• Notepad++ plugin for viewing JSON data and other benefits
• Excel spreadsheet to generate SQL