We help IT Professionals succeed at work.
Get Started

The trigger fails because key column is not part of the deleted table

Mehram asked
Last Modified: 2014-08-11
Sql Server 2005

use shma_grmn_korangi
      [CA_ACC_CODE] [varchar](20) NOT NULL,
      [console_code] [varchar](11) NULL,
      [CA_TITLE] [varchar](40) NULL)

insert table GL_CHARTOFACC ( ca_acc_code, ca_title,console_code)
select '27178250004','abc ltd','27150701914'

USE [shma_console]
      [CA_ACC_CODE] [varchar](20) NOT NULL,
      [CA_TITLE] [varchar](40) NULL,
      [dlt_matched] [tinyint] NULL)
insert table GL_CHARTOFACC ( ca_acc_code, ca_title,dlt_matched)
select '27150701914','abc ltd',1

what I want: No row in shma_grmn_korangi should be updated
if dlt_matched has a value 1.

I tried
use shma_grmn_korangi
create trigger  [dbo].[gl_chartofacc_mapping_locked_dlt_matched]
on [dbo].[GL_CHARTOFACC]
for update AS

if  exists
                        select null
                        from deleted d
                join gl_chartofacc c on ( d.ca_acc_code = c.ca_acc_code)
                join shma_console.dbo.gl_chartofacc cons on ( c.console_code = cons.ca_acc_code)
                   where 1=1
                and cons.dlt_matched = 1
                               RAISERROR ('This code cannot be edited/remapped/deleted as it is tallied as per audited balance sheet 30-06-2013 Deloitte', 16, 1)
                        ROLLBACK TRANSACTION

update gl_chartofacc set console_code ='xx' where ca_acc_code ='27178250004'
result: ( 1 record updated)
means the trigger failed
probably because
console_code is not part of deleted table. If it had been so, I can join directly
  on d.console_code = cons.console_code
and the trigger would have worked.

Since, gl_chartofacc is being updated by thirdy party software, I cannot make cosole_code present in
deleted table.

How to resolve ?
what is the wrokaround.?
Watch Question
Senior Developer
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE