[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-11
3
Medium Priority
?
152 Views
Last Modified: 2014-08-11
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.?
0
Comment
Question by:Mehram
  • 2
3 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40252863
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
 

Author Comment

by:Mehram
ID: 40252909
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
 

Author Closing Comment

by:Mehram
ID: 40252917
many many thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question