Changing the default behavior of DML statements (INSERT/UPDATE/DELETE)

Generally we are using DML statements like Insert for inserting data into database table likewise Update for updating existing data in database table, but it is also possible to change the default behavior INSERT/UPDATE/DELETE (DML) statements for example while inserting data using INSERT statement, the new record may applies as an update to the table or even applies as a delete to the table.

You can implement the changing behavior with INSTEAD OF TRIGGER. Let’s do some practical to achieve the changing behavior of DML statements.

use testDemo

go

if object_id('ChangingDMLBehavior') is not null

      drop table ChangingDMLBehavior

go

     

We were checking the existence of 'ChangingDMLBehavior' table object.

Now lets create a table 'ChangingDMLBehavior'

create table ChangingDMLBehavior

(id int not null identity(1,1),

name varchar(10))

Command(s) completed successfully.

Table is now ready; it’s time to write an INSTEAD OF TRIGGER to stimulate the changing behavior of INSERT statement.

 create trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      select * from inserted

      select * from deleted

      return

end

go

Command(s) completed successfully.

Now since Trigger is in place, now it’s time to try out INSERT statement on 'ChangingDMLBehavior' and check out the behavior of INSERT Statement.

 insert into ChangingDMLBehavior(name) values('Binny')

Now querying the table to find the inserted records, to our shock we found no records.

select * from ChangingDMLBehavior

id          name

----------- ----------

(0 row(s) affected)

This is because a record is inserted to table 'ChangingDMLBehavior' with INSERT command, but nothing happens to the table due to the instead of trigger. This is because the trigger does not tell SQL Server to perform any data manipulation operation.

Let’s do some modification of INSTEAD OF TRIGGER to allow INSERT OPTION on table.

alter trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      set nocount on

      insert into ChangingDMLBehavior(name)

            select name from inserted

      return

end

go

Command(s) completed successfully.

Now we can fire INSERT statement on 'ChangingDMLBehavior' table and can expect insertion of record to the table. Lets insert one record into 'ChangingDMLBehavior'.

insert into ChangingDMLBehavior(name) values('Prachi')

(1 row(s) affected)

Querying the table we get

select * from ChangingDMLBehavior

id          name

----------- ----------

1           Prachi

(1 row(s) affected)

Instead of triggers are useful when you want some setting kind of table in database to hold the defined values.

When you are in love with your code you will stop dreaming code with closed eyes because reality better than your dreams

Post Reference: Vikram Aristocratic Elfin Share

Batch Delete – “One spoon at a time” the smart DELETE

BATCH DELETE

Delete in SQL server is not as simple as it looks.

Yes it has simple line of statement.

delete BatchDeleteDemo_Tab where name='Aadarshini'

Then what is the issue, there are several problem like

• transaction log
• disk space
• time consuming
• locking

It depends how many rows are affected by the DELETE statement above, but generally, it is almost never a good idea to run a ‘straight forward’ deletes.

SQL Server is working in a transactional manner, the rows which are to be deleted have to be logged in the transaction log, and if there is not enough disk space for the log, then we have a real problem – the log is full, no other transactions can be run… you get the idea.

When you delete rows from a table, the rollback transaction information is always written fully to the log file, and it will grow until the transaction is committed or rolled back.

So what is the option left for us:

while (1=1)

begin

      delete top 3 BatchDeleteDemo_Tab where name='Aadarshini'

      If @@rowcount = 0

            BREAK

      ELSE

            Continue

End

The while loop will delete 3 record at a time and then check if there are more rows to delete, if there are not then the loop gets terminated

CONCLUSION: This method is better and more efficient for obvious reasons: the log does not grow by too much and the log space is reused; it is easy to terminate the delete quickly and only the transaction of the last batch will be reverted; the lock escalations do not have a huge impact on the entire system since it is for few rows at a time.  

Post Reference: Vikram Aristocratic Elfin Share

Case with Update statement Or Conditional Update

There are certain situation where we need some updation  on the basis of condition. For example  if name contain ‘Kruthika’ update it to ‘Kruthik’.

Our table is containing the following records

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

You might be thinking this update can be accomplished with the help of were condition in update statement. Correct it goes like this

update user_tab

set user_name = 'Kruthik'

where user_name = 'Kruthika'

(1 row(s) affected)

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthik                                            1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

But consider a situation where you need to update ‘Trishona’ with ‘Trishon’ and ‘suranjan’ with ‘suranjana’.

Is it possible with WHERE clause in Update statement. ANSWER IS NO.

Then what is option left for such update, Here comes CASE, let see how this update can be done with CASE

update user_tab

set user_name = (case user_name

                              when 'Trishona' then 'Trishon'

                              when 'suranjan' then 'suranjana'

                              else user_name end)

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishon                                            1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjana                                          1

(11 row(s) affected)

I really like this keyword a lot of thing can be accomplished with CASE.

Post Reference: Vikram Aristocratic Elfin Share

Make use of Magic tables with output clause while inserting/updating or Deleting record in your database table objects

There are two magic table provided by SQL server- Inserted and Deleted.

Inserted and Deleted: The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert transaction, new rows are added to the inserted table.  In case of update transaction which is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table.

OUTPUT Clause: The clause returns a copy of the data that you’ve inserted into or deleted from your tables.  OUTPUT clause has accesses to inserted and deleted tables, OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

Let’s explore this with the help of an example:

For our explanation we have taken a table ‘User_Tab’ having the following records in it.

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(11 row(s) affected)

INSERT : Now we are trying to insert a new record in it. See the below mentioned code, here we are using OUTPUT clause to show the value which are going to inserted into the table

insert into user_tab(user_name,active)

output INSERTED.user_id,INSERTED.user_name

values ('suranjan','True')

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

DELETE: Here we are using OUTPUT clause with Deleted magic table of Sql Server to fetch the deleted value.

DELETE user_tab

OUTPUT deleted.user_id,deleted.user_name

WHERE user_name='suranjan'

user_id     user_name

----------- --------------------------------------------------

16          suranjan

(1 row(s) affected)

UPDATE:  In case of update, the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table. See the below code

update user_tab

set user_name = 'Phaneesh'

output deleted.user_name as 'PreviousName' ,inserted.user_name as 'NewName'

where user_name = 'Fani'

PreviousName                                       NewName

-------------------------------------------------- --------------------------------------------------

Fani                                               Phaneesh

(1 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share