Understanding DELETE and DROP command

Question

I am a student of Computer Science. Recently I am trying to learn database. I found a link describing about the difference between DELETE and DROP command. I understand most of the difference between them with the most important one - DELETE removes only data where DROP removes the table/object too.

But I can not understand one difference - DELETE command fire a DML trigger where DROP doesn't fire any DML trigger. The link only state this fact without any explanation. Can someone explain it a bit, so that I can understand it more.

The DELETE statement runs within the context of a transaction boundary, which can be committed or rolled back. The DROP statement which is DDL as opposed to DML cannot be undone with a roll-back. — crowne, Apr 26 '15 at 10:14
`DROP` is a DDL statement and DDL statements never fire DML triggers — , Apr 26 '15 at 10:19

score 5 · Answer 1 · answered Apr 25 '15 at 17:55

5

DROP removes table along with data and other structures, such as constraints, indexes, grants and also triggers. So trigger doesn't fire because in process it is also removed.

answered Apr 25 '15 at 17:55

Janis Baiza

951
6
15

But I found TRUNCATE doesn't fire any trigger. But TRUNCATE only removes the data while it leaves the table unchanged. So how it possible for TRUNCATE or am I getting wrong/missing something? – KajolK Apr 25 '15 at 18:00
1

There are two ways of operations - DML - Data manipulation (language) and DDL - data definition (language). INSERT, UPDATE, DELETE are DML operations and will fire DML trigger. CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE are DDL operations and will not fire DML trigger. – Janis Baiza Apr 25 '15 at 18:06

score 3 · Answer 2 · answered Apr 25 '15 at 17:54

3

DELETE keeps the table but removes some or all rows in it. DROP removes the table along with all its data, such that the table no longer exists.

answered Apr 25 '15 at 17:54

Gabe

84,912
12
139
238

score 2 · Accepted Answer · edited Apr 25 '15 at 20:33

"DELETE command fire a DML trigger where DROP doesn't fire any DML trigger"'

In Oracle & some other DB provides triggers. Triggers are noting but subroutines which will get executed on DML actions like INSERT, UPDATE, DELETE .. say on updating some row in a table, a trigger can be invoked.

Similarly when you execute delete statement, a trigger can be executed. To get more understanding read through triggers.

score 2 · Answer 4 · answered Apr 26 '15 at 09:47

delete and name explains will delete all or some rows from a table depending on your specifications and requirements, where as drop will remove the table from the schema.

As for triggers, here is an example.(They only fire on dmls, not ddls,) I have a table called testmp2 which has the employee details and another table testdelvalues which is used to store only the deatils of deleted employee records.

Here is the example trigger I'm using.

SQL> CREATE OR REPLACE TRIGGER testempdel
  2  AFTER delete ON testemp2
  3   FOR EACH ROW
  4  BEGIN
  5      INSERT INTO testdelvalues (empno,empname) values (:Old.empno,:Old.ename);
  6  END;
  7  /

Trigger created.

These are all the records from employee table.

SQL> select * from testemp2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

Currently the testdelvalues values is empty.

SQL> select * from testdelvalues;

no rows selected

Now I delete a record from the table, which will fire a delete trigger to store the values into the testdelvalues , storing which were the deleted employee empno and name.

SQL> delete from testemp2 where empno=7369;

1 row deleted.

SQL> commit;

Commit complete.

The details recorded , which were inserted using the delete trigger.

SQL> select * from testdelvalues;

     EMPNO EMPNAME
---------- ----------
      7369 SMITH

Understanding DELETE and DROP command

4 Answers4