Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
What's wrong with this trigger code?

Answered What's wrong with this trigger code?

  • Sunday, January 06, 2013 3:56 PM
     
      Has Code
    Sign In to Vote
    0

    My code is:

    ALTER TRIGGER [dbo].[EntityAgencyINS] 
   ON  [dbo].[Contract]
   AFTER  INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
declare @AgencyEntityID int
select top 1 @AgencyEntityID = EA.AgencyEntityID 
from 
EntityAgency as ea
inner join
Inserted as I on i.AgencyEntityID = EA.AgencyEntityid
			
if (@@ROWCOUNT = 0) 
BEGIN
	insert into EntityAgency (AgencyEntityID) select agencyEntityID from Inserted

    END
    END

    The Problem:  If the AgencyEntityID does exist, I'm getting an error message that the id already exists in the EntityAgency table.

    What I think should happen:  The INSERT should only happen when @@RowCount = 0.  If @@RowCount > 0, then ignore then INSERT


    Thank you!

    Marion in Rochester

     

All Replies

  • Sunday, January 06, 2013 4:02 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Try this code to only insert new entities:

    SET NOCOUNT ON;

;with NewEntities AS (select agencyEntityID from Inserted I

WHERE NOT EXISTS (select 1 from EntityAgency EA where EA.AgencyEntityID = I.AgencyEntityID))

INSERT INTO EntityAgency(AgencyEntityID) select DISTINCT AgencyEntityID from NewEntities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



     
  • Sunday, January 06, 2013 4:08 PM
     
     
    Sign In to Vote
    0

    You're aware of the fact, that a trigger is called per statement? Thus your TOP 1 will select an arbitrary entity when more than one row is inserted at once..

     
  • Sunday, January 06, 2013 6:54 PM
     
     
    Sign In to Vote
    0

    Thanks Naomi - but I'm not quite there.

    When I update rows in the Contract table, the trigger runs - but it tries to insert into the EntityAgency table even if the row already exists, so I get an error

    Msg 2627, Level 14, State 1, Procedure EntityAgencyINS, Line 27

    Violation of PRIMARY KEY constraint 'PK_EntityAgency'. Cannot insert duplicate key in object 'dbo.EntityAgency'. The duplicate key value is (2957).

    The statement has been terminated.

    Marion in Rochester

     
  • Sunday, January 06, 2013 7:04 PM
    Moderator
     
     
    Sign In to Vote
    0
    This is strange - the query I posted will only get new rows which do not already exist in the EntityAgency table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 9:39 PM
     
     Answered Has Code
    Sign In to Vote
    0

    My guess is you are entering the same AgentEntityID multiple times into dbo.Contract with the same insert statement.  But you only want to enter it once into EntityAgency (and only when it doesn't already exist in EntityAgency.  To try


    SET NOCOUNT ON;

;with NewEntities AS (select agencyEntityID from Inserted I

WHERE NOT EXISTS (select 1 from EntityAgency EA where EA.AgencyEntityID = I.AgencyEntityID))

INSERT INTO EntityAgency(AgencyEntityID) select DISTINCT AgencyEntityID from NewEntities

     
  • Sunday, January 06, 2013 10:37 PM
     
     
    Sign In to Vote
    0

    Yes Tom - that helped.

    You were 100% correct on your guess about what I was trying to do.

    Now I have a different trigger not working but I'll come back with that separately.

    Marion in Hudson Valley

     
  • Sunday, January 06, 2013 11:31 PM
     
     
    Sign In to Vote
    0
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you do not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Sample data is also a good idea, along with clear specifications. 

    Good SQL programmers use DRI actions and constraints to maintain data integrity. They want to get rid of triggers and other procedural code dinosaurs form the 1970's. Another sign of bad SQL is local variables, loops and IF-THEN control flow logic to mimic procedural languages.This is COBOL or BASIC written in T-SQL. 

    What is an “Entity_Agency”? The name makes no sense in RDBMS. The mix of meta data and other things seems to imply this might be OO being rammed into SQL. 

    Why not use  MERGE to insert new agencies into an Agencies table? The skeleton (no DDL given!) is something like this: 

    MERGE INTO Agencies AS A
    USING (VALUES (@in_agency_id) AS X(agency_id)
       ON A.agency_id = X.agency_id
    WHEN NOT MATCHED
    THEN INSERT VALUES (@in_agency_id)
    WHEN MATCHED
    THEN UPDATE 
           SET ..; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Monday, January 07, 2013 1:36 AM
    Moderator
     
     
    Sign In to Vote
    0

    >They want to get rid of triggers and other procedural code dinosaurs form the 1970's.

    Nothing wrong with stored procedure usage. It is better to put the code on the server-side rather than on the client-side if feasible.  Stored procedures are very efficient since they are compiled and process on the server without client-server network communications.

    Triggers are a different story....

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012