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

Sql Server 2005



use shma_grmn_korangi
CREATE TABLE [dbo].[GL_CHARTOFACC](
      [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]
CREATE TABLE [dbo].[GL_CHARTOFACC](
      [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
            )
                  begin
                               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
                  end


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.?
MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
D'oh? But I guess you mean:
USE tempdb;

CREATE TABLE dbo.GL_CHARTOFACC_OTHERDB
    (
      CA_ACC_CODE VARCHAR(20) NOT NULL ,
      console_code VARCHAR(11) NULL ,
      CA_TITLE VARCHAR(40) NULL
    );

CREATE TABLE dbo.GL_CHARTOFACC_CONSOLE
    (
      CA_ACC_CODE VARCHAR(20) NOT NULL ,
      CA_TITLE VARCHAR(40) NULL ,
      dlt_matched TINYINT NULL
    );
GO

CREATE TRIGGER dbo.gl_chartofacc_mapping_locked_dlt_matched ON dbo.GL_CHARTOFACC_OTHERDB
    FOR UPDATE
AS
    SET NOCOUNT ON;
    IF EXISTS ( SELECT  *
                FROM    DELETED O
                        INNER JOIN GL_CHARTOFACC_CONSOLE C ON O.console_code = C.CA_ACC_CODE
                WHERE   C.dlt_matched = 1 )
        BEGIN
            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
        END;
GO

PRINT 'Inserts..';
INSERT  INTO GL_CHARTOFACC_OTHERDB
        ( ca_acc_code ,
          ca_title ,
          console_code
        )
VALUES  ( '27178250004' ,
          'abc ltd' ,
          '27150701914'
        );
		
INSERT  GL_CHARTOFACC_CONSOLE
        ( ca_acc_code ,
          ca_title ,
          dlt_matched
        )
VALUES  ( '27150701914' ,
          'abc ltd' ,
          1
        );
GO

SELECT  *
FROM    GL_CHARTOFACC_OTHERDB O
        INNER JOIN GL_CHARTOFACC_CONSOLE C ON O.console_code = C.CA_ACC_CODE;
GO

PRINT 'Updating..';  
UPDATE  dbo.GL_CHARTOFACC_OTHERDB
SET     console_code = 'xx'
WHERE   ca_acc_code = '27178250004';

UPDATE  dbo.GL_CHARTOFACC_OTHERDB
SET     CA_TITLE = '321';
GO

SELECT  *
FROM    GL_CHARTOFACC_OTHERDB O
        INNER JOIN GL_CHARTOFACC_CONSOLE C ON O.console_code = C.CA_ACC_CODE;
GO

DROP TABLE dbo.GL_CHARTOFACC_CONSOLE;
DROP TABLE dbo.GL_CHARTOFACC_OTHERDB;
GO

Open in new window


The problem is that you test instead of DELETED against the alread changed table in your JOIN condition.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MehramAuthor Commented:
I love u!!!

I did
update gl_chartofacc set console_code =null where ca_acc_code ='27178250004'
it worked

I did
update gl_chartofacc set console_code = 'abc' where ca_acc_code ='27178250004'
it worked

and i think this would also work ( because deleted is same in case of update, dlete)

delete from gl_chartofacc where ca_acc_code ='27178250004'

i only need to add
for update, delete

Great work!!!

I am glad, because, I need to create this trigger in 24 different database.
0
MehramAuthor Commented:
many many thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.