Triggers in Oracle database

Oracle engine allows users to define procedures that are implicitly execute, when an insert, update or delete operation issued against a table. These procedures are called database triggers.
They are standalone and are fired implicitly by the oracle engine itself.

These database objects or triggers consists of :

• A named database event and
• A PL/SQL block that will execute when the event occur

When a trigger is fired, SQL statement inside the trigger's PL/SQL block can also fire the same or other trigger. This is called 'cascading triggers'.

Triggers has three basic points:

1. Trigger event or statement: It is a sql statement that causes a trigger to be fired. It can be 'INSERT', 'UPDATE', or 'DELETE'statement for specified table.
2. Trigger Restriction: A trigger restriction specifies a Boolean expression that must be TRUE for the trigger to be fired. A Trigger restriction is specified using a WHEN clause
3. Trigger Action: It is the PL/SQL code to be executed when a triggering statement is encountered and any trigger restriction evaluated to TRUE

We can create trigger with the same name as table, procedure or function

Triggers are used to maintain data redundancy in the database

When any DML operation is performed and we want to do something against it we fired trigger

PL/SQL block of Trigger can not contains transaction control sql statement(commit, rollback, savepoint)

Syntax for Creating/updating a Trigger. To update a trigger we use Replace keyword

CREATE OR REPLACE TRIGGER triggerName

{ BEFORE, AFTER }

{ INSERT, DELETE, UPDATE [ of col1, col2.... ] }

ON tableName

[ REFERENCING { OLD AS old, NEW AS new }]

[ FOR EACH ROW [ WHEN condition ] ]

DECLARE

                  variable declaration;

                  constant declaration;

BEGIN

                  PL/SQL statement;

EXCEPTION

                  exception PL/SQL block;

END;

'FOR EACH ROW' designate the trigger to be a Row trigger. if this clause is omitted, the trigger is called Statement trigger

Oracle have below types of triggers:

• ROW Trigger: A row trigger is fired each time a row in the table is affected by the triggering statement. For eg. if and UPDATE statement update multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement
• STATEMENT Trigger: A statement trigger is fired once on behalf of the triggering statement, independent of the number of rows affected by triggering statement(even if no row updated).
• BEFORE v/s AFTER Trigger:When defining a trigger it is necessary to specify the trigger time ie, specifying when the triggering action is to be executed in relation to the triggering statement. BEFORE and AFTER apply to both and the statement trigger. 

So with above statement, I can say that oracle have 12 type of triggers

1. Before Insert Row trigger
2. Before Update Row trigger
3. Before Delete Row trigger
4. Before Insert Statement trigger
5. Before Update Statement trigger
6. Before Delete Statement trigger
7. After Insert Row trigger
8. After Update Row trigger
9. After Delete Row trigger
10. After Insert Statement trigger
11. After Update Statement trigger
12. After Delete Statement trigger

Delete trigger:
DROP TRIGGER triggerName

Below line of code will show the status of trigger
SELECT STATUS triggerName

Below line  will Enable or Disable trigger
ALTER TRIGGER triggerName DISABLE/ENABLE
this will enable or disable trigger specified with triggerName only

To enable or disable all trigger use below command
ALTER TABLE tableName ENABLE/DISABLE all trigger

Understanding Oracle SELECT SQL Query Syntax

One SELECT SQL query consists of below keyword.

• SELECT : it list all column to be shown in resultant set
  SELECT empName, empId FROM employee;

•  FROM : consist of one or more table name from which you want to get the result
  
  SELECT e.empName, e.empId, e.departmentId,  d.departmentName FROM employee e, department d;

•   WHERE : it is used to filter the result set
  
  SELECT Lname, Fname FROM employee WHERE Lname = ‘Yadav’;

•  GROUP BY : it is used in combination with Aggregate functions such as COUNT, SUM, AVG
  
  SELECT * FROM employee GROUP BY departmentId;

•  HAVING: this is again use to filter the result set and similar to Where clause. In WHERE clause the search condition on the row is performed before rows are grouped whereas in cas of HAVING, groups are formed first and then search condition is applied to the group. You can use WHERE & HAVING clause together. Only columns in GROUPBY statement is used for the HAVING clause.
  
  SELECT * FROM employee GROUP BY departmentId HAVING departmentId = 10;

•  ORDER BY: it is used to arrange the result set in ascending order or descending order
  SELECT Lname, Fname FROM employee ORDER BY Lname DESC;

•  STARTING WITH: It works with the conjunction with the WHERE clause. It is similar to like clause
  SELECT Lname, Fname FROM employee WHERE Lname STARTING WITH (‘Y%’);

Similarly we have more keywords like BETWEEN, IN, LIKE and many more. But above one are mainly used keywords in SELECT sql

Basic unix commands

Below are some useful Unix commands

Move Cursor

Command	Description
k	Move cursor up one line
j	Move cursor down one line
h	Move cursor to the left one character position
l	Move cursor to right one character position
0 or |	Put cursor at beginning of line
$	Put cursor at end of line
w	Move cursor to  next word
b	Move cursor to previous word
(	Move cursor to beginning of current sentence.
)	Move cursor to beginning of next sentence
G	Move cursor at the last line of the file
H	Move cursor to top of screen

Insert Text

Command	Description
i	insert text before current cursor location
a	insert text after current cursor location
o	Creates a new line for text entry below cursor location

Delete Line

Command	Description
x	Deletes character under cursor position
X	Delete character before cursor position
dw	Deletes the current word
d$	delete all content to the right of the cursor
dd	Deletes the current line

Copy and Paste

Command	Description
yy	Copy current line
yw	Copy word
p	paste last copied/cut/deleted text after current cursor position

Some other useful commands

Command	Description	Example
cat	display file contents or Copy one or more file content to another file	Cat Cat fiel1 file2 > fileNew
chgrp	change file group
chmod	Changes the read, write and execute permissions for the file	chmod [mode] fileName
cp	copy source file into destination	cp [file1] [directory]/[file2]
grep	Search content in file	grep ^H fileName : It list all words start with H
head	display first 10 lines of a file	head fileName
tail	display last 10 lines of file	tail fileName
ls	display content of directory	ls
mkdir	make directory	mkdir dirName
cd	change directory	cd dirName
more	display data in paginated form
mv	move or rename file form old to new	mv file1 file2
rm	remove/delete file	rm fileName
rmdir	Removes or erases empty directory. You must first use `rm *' to empty the file	rmdir dirName
pwd	print current working directory	pwd
wc	count words. Lines and characters
who	Displays currently logged in users.	who
who am i	Displays current user id and access.	who am i