Calling a procedure from Trigger?

With a small demonstration we will see how we can call a stored procedure from a trigger.

Here we are creating a table Employee

create table tbl_Employee

(id int identity(1,1),

name varchar(20))

Command(s) completed successfully.

Now lets insert couple of records into newly created table.

insert into tbl_Employee

select 'Akansha'  union all

select 'Aurpita'

Since our table is ready, we can now create a procedure, which we will be calling inside a trigger.

create procedure sp_DisplayEmployee

as

       select * from tbl_Employee

go

Command(s) completed successfully.

At this point, we have everything ready, table, procedure, so now turn to create trigger on table Employee for Insert and Update operation and call sp_Display function in it.

We have very simple logic inside trigger i.e. whenever any insert or update commands fired on Employee table, this trigger will comes into picture and display the original data in the table.

create trigger trg_Employee

on tbl_Employee

for insert,update

as

       exec sp_DisplayEmployee

go

Command(s) completed successfully.

Our trigger is now ready, lets fire update statement to see whether it will fire associated trigger.

update tbl_Employee

set name = 'Lopamudra'

where id =4

id     name

1      Akansha

2      Aurpita

Great, so with update statement, it fires trigger and in return trigger call sp_DisplayEmployee stored procedure to display records.

Conclusion: You can call procedure inside trigger.

Get your washroom ready for your coding :)

Post Reference: Vikram Aristocratic Elfin Share

Allowing only two null values for a column in SQL table

My friend Subhasis Swain asked me yesterday night, Vikram, is it possible to restrict table column to have only two null values.

Though I don’t find any practical carrying out for the same, but yes we can create our logic to hold only two null values in a column.

 I said Yes Subhasis, but alas I invited sleepless night agreeing to write code for the same on SQL Server 2014, which was his demand to download and install at night 2:00 AM

Along with it, there was one more condition, If I succeed then I will give cold coffee treat to him and Shankhodeep Karmakar, one of my very good friend at CCD that to 2/3 :-O

Let’s see how we can do this, here for the demo purpose I am creating a table with two column id and name.

create table TAB_TwoNullOnly

(id int,

name varchar(20))

go

Now I am trying to write a logic using trigger to restrict two null values for name field.

create trigger TRG_AllowTwoNullValue on TAB_TwoNullOnly

for insert,update

as

       declare @row_count int

       select @row_count = COUNT(*) from TAB_TwoNullOnly where name is null

       if @row_count > 2

       begin

              rollback

              print 'you cannot have more then two null values'

       end

       print 'After trigger fired.'

go

Here this trigger is written for insert and update DML statement so when a user fire insert or update table, the trigger will come into picture.

Inside the trigger we are checking how many null values are present in table including the latest insert and update, and if it is  greater then 2 then we do rollback our transaction which insert null in name field else allow the insert or update operation to perform.

Now since trigger is in place, let’s insert data in table.

insert into TAB_TwoNullOnly values(1,'Shibani')

insert into TAB_TwoNullOnly values(2,null)

insert into TAB_TwoNullOnly values(3,null)

Now we have inserted two null values in name field

select * from TAB_TwoNullOnly

id          name

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

1           Shibani

2           NULL

3           NULL

Now if we try to insert one more record with null value in Name column, it should throw error. Lets try out

insert into TAB_TwoNullOnly values(4,null)

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 6

The transaction ended in the trigger. The batch has been aborted.

Here we saw that it is not allowing me to enter null value because already there are two null value present in the Name column.

Lets now update the name field with null where Id =1, again it should not allow me to update since updating value will make the count of null value to 3, which is not comply with the problem statement.

update TAB_TwoNullOnly

set name = null

where id=1

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 8

The transaction ended in the trigger. The batch has been aborted.

So here saw with the help of trigger we can achieve such kind of constraint on table values.

Now today I m puting in order for 2/3 coffee at CCD with Subhasis and Sankhodeep. :D

This dawn my code was so loud on bed, it woke up my roommate. I said him to rollover and go back to sleep ;) ;)

Auditing only updated column with Trigger

Read Prev Project: Auditing Update and Delete Operation with Trigger

When you want to Audit only the column which gets updated, you require something called update () function to find the column which gets updated.

I am writing this article because today I earshot something like this from a conversation which was going between an SQL Expert Manish Sharma and a Project team of iGATE. Their discussion influences me to write up this article. Thanks guys n sorry for listening your discussion;-P, anyways your discussion made an increment to my article list. 

Here I have a table emp with following data.

select * from emp

emp_id      ename      dept_id

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

1           neha       1

2           richa      1

3           sidhika    2

4           sapna      2

5           sneha      3

6           Kavita     3

7           Binny      1

(7 row(s) affected)

Now I am creating a Audit table which will hold the old values of update or delete operation.

Create table Tab_MyAudit1

(    

      A_Action char(1) not null,

      A_AuditDate datetime not null,

      A_Table     varchar(15) not null,  

      A_Values xml

)

Command(s) completed successfully.

My Objective: To write a trigger which will insert only the old column values which gets updated with update statement, no need to record other column values which are not the part of update statement. And in case of delete record all column values.

Now here we are creating a trigger which is using Update() function to check which column gets updated, accordingly it insert the values to Audit table.  The if (SELECT COUNT(*) FROM inserted) > 0 will tell whether the DML operation is Update or Delete. In case of Update INSERTED table will have data so this condition will set to true whereas, in case of delete, only Deleted table will hold data, so this condition will set to false and the pointer will go to else part.

create trigger TGR_EMP_UpdateDeleteLog1

ON emp

for UPDATE,DELETE

AS

begin

      Declare @AuditAction varchar(1)

      Declare @OldMessage varchar(200)

     

      --Condtion for U or D

      if (SELECT COUNT(*) FROM inserted) > 0

      begin

            set @AuditAction = 'U'

            set @OldMessage =

(select  (case

            when update(ename)then deleted.ename end) as OldName,

            (case

when update(dept_id)then deleted.dept_id end) as OldDeptId

            from deleted

            inner join emp

            on deleted.emp_id=emp.emp_id

            FOR XML AUTO)

      end

      else

      begin

            set @AuditAction = 'D'

            set @OldMessage =

            (SELECT * from deleted FOR XML AUTO)

      end

      insert into Tab_MyAudit1 values

      (@AuditAction,getdate(),'EMP',@OldMessage)

End

Command(s) completed successfully.

Now our Trigger is ready, let’s test it with DML statement, before that lets query Audit table and emp table.

select * from Tab_MyAudit

(0 row(s) affected)

So there is no row in Audit table Tab_MyAudit for now. Lets query Emp table too

select * from emp

emp_id      ename      dept_id

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

1           neha       1

2           richa      1

3           sidhika    2

4           Pragyan    2

5           sneha      3

6           Kavita     3

(6 row(s) affected)

Update Operation

Now since everything is in place, let’s fire an Update DML where we are updating employee name to Sneha where the employee id is 4.

update emp

set ename = 'Sneha' where emp_id = 4

let’s check Audit table, the Audit table should record only old Name which gets changed through Update statement. i.e “Pragyan” should get inserted in Audit table.

select * from Tab_MyAudit1

A_Action A_AuditDate             A_Table A_Values

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

U        2013-12-30 22:11:26.123 EMP     <deleted OldName="Pragyan"/>

(1 row(s) affected)

So to our expectation we found correct entry in Audit table. Let’s query Emp table too to check the update.

select * from emp

emp_id      ename      dept_id

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

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      2

5           sneha      3

6           Kavita     3

(6 row(s) affected)

Now we have two employee with employee name ‘Sneha’ but different dept_id i.e. 2 and 3 , lets update the dept Id to 1 where employee name is ‘Sneha’

update emp

set dept_id = 1 where ename = 'Sneha'

Now let’s query  the Audit table, our expectation is, the department which gets updated to 1 are 2 and 3, so 2 and 3 should gets inserted in a single row in audit table.

select * from Tab_MyAudit1

A_Action A_AuditDate A_Table A_Values

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

U        2013-12-30  EMP   <deleted OldName="Pragyan" />

U        2013-12-30  EMP   <deleted OldDeptId="2"/><deleted OldDeptId="3" />

(2 row(s) affected)

So to our expectation it records both the department id as we can see the second record. Also let’s query emp table to check the update.

select * from emp

emp_id      ename      dept_id

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

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

5           sneha      1

6           Kavita     3

(6 row(s) affected)

Delete Operation

Now here we are trying to delete employee with employee id 5 and will check whether the whole record values gets stored in Audit Table.

delete emp where emp_id = 5

Let’s query Audit table

select * from Tab_MyAudit1

A_Action A_AuditDate A_Table A_Values

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

U        2013-12-30 EMP    <deleted OldName="Pragyan" />

U        2013-12-30 EMP    <deleted OldDeptId="2"/><deleted OldDeptId="3"/>

D        2013-12-30 EMP    <deleted emp_id="5" ename="sneha" dept_id="1"/>

(3 row(s) affected)

So to our expectation as we can see the third record, it records all column value in Audit table. Lets query our Employee table to see the update.

  

select * from emp

emp_id      ename      dept_id

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

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

(5 row(s) affected)

Take a good look at Programmer, he will take care of your needs, code has answer to every problem.

Post Reference: Vikram Aristocratic Elfin Share