How to create a trigger for add or update main table and supertype.

  • Hello, 
    I have a question about creating a trigger or stored procedure for updating 2 tables at once. 

    I have a supertype that is Subsystem and I have 4 subtypes,  that are  Engine, Transmission, Hydraulic, and Auxiliary.

    In my Subsystem I only  have 1 attribute and that's idSubsystem <identity>   

    Here is my SP for add/update of the engine. 

    DROP PROCEDURE IF EXISTS sp_add_or_update_engine
    GO
    CREATE PROCEDURE sp_add_or_update_engine
        @idEngine                    int,
        @idSubsystem                int,
        @Engine_Part_Number            int,
        @Engine_Revision_Number        varchar(20),
        @Engine_Name                varchar(50),
        @Engine_Description            varchar(7000),
        @update                        BIT
    AS
    BEGIN
        BEGIN TRY
            IF (@update = 0)
            BEGIN
                INSERT INTO Engine (idSubsystem, Engine_Part_Number, Engine_Revision_Number, Engine_Name, Engine_Description ) VALUES
                (@idSubsystem, @Engine_Part_Number, @Engine_Revision_Number, @Engine_Name, @Engine_Description)
            END
            ELSE
            BEGIN
                IF (@idEngine IS NULL OR @idEngine = 0)
                BEGIN
                    ;THROW;THROW 50000, '@idEngine cannot be NULL if an UPDATE is to be commenced.', 1
                END
                IF NOT EXISTS (SELECT 1 FROM Engine WHERE IDEngine = @idEngine)
                BEGIN
                    ;THROW;THROW 50001, 'This Engine does not exist.', 1
                END
                
                UPDATE ENGINE SET         
                idSubsystem = @idSubsystem,                
                Engine_Part_Number = @Engine_Part_Number,
                Engine_Revision_Number = @Engine_Revision_Number,
                Engine_Name = @Engine_Name,
                Engine_Description = @Engine_Description
                WHERE idEngine = @idEngine
            END

        END TRY
        BEGIN CATCH
            ;THROW
        END CATCH
    END
    GO

    Now my Question is how can I insert the max id of Subsystem into this sp?   Like if I have already 205 Subsystems and i want to add a new engine that the idSubsystem inside engine table will be 206 and not 1 or 2.

  • If I've understood correctly, all you need to do is to INSERT DEFAULT VALUES into subsystem and use an OUTPUT clause to get the ID, which you can use in the row that you insert into Engine.

    John

  • Hello John, 

    Now I have some more time, so I will try to explain it all clearly. 

    So I have made a conceptual data model where I have a supertype Subsystem and 2 subtypes Engine and Transmission.  It is Total and Exclusive so every subsystem has to be one of these 2. 

    CDM looks like this:

    I Converted it into PDM ( physical data model in Power designer 16.5) 

    I also made a Stored Procedure for insert of the data.  This one is for the engine.  It's the same as above

       
    /*    add_or_update_engine        */

    DROP PROCEDURE IF EXISTS sp_add_or_update_engine
    GO
    CREATE PROCEDURE sp_add_or_update_engine
        @idEngine                    int = NULL,
        @idSubsystem                int,
        @Engine_Part_Number            int,
        @Engine_Revision_Number        varchar(20),
        @Engine_Name                varchar(50),
        @Engine_Description            varchar(7000),
        @update                        BIT
    AS
    BEGIN
        BEGIN TRY
            IF (@update = 0)
            BEGIN
                INSERT INTO Engine (idSubsystem, Engine_Part_Number, Engine_Revision_Number, Engine_Name, Engine_Description ) VALUES
                (@idSubsystem, @Engine_Part_Number, @Engine_Revision_Number, @Engine_Name, @Engine_Description)
            END
            ELSE
            BEGIN
                IF (@idEngine IS NULL OR @idEngine = 0)
                BEGIN
                    ;THROW 50000, '@idEngine cannot be NULL if an UPDATE is to be commenced.', 1
                END
                IF NOT EXISTS (SELECT 1 FROM Engine WHERE IDEngine = @idEngine)
                BEGIN
                    ;THROW 50001, 'This Engine does not exist.', 1
                END
                
                UPDATE ENGINE SET         
                idSubsystem = @idSubsystem,                
                Engine_Part_Number = @Engine_Part_Number,
                Engine_Revision_Number = @Engine_Revision_Number,
                Engine_Name = @Engine_Name,
                Engine_Description = @Engine_Description
                WHERE idEngine = @idEngine
            END

        END TRY
        BEGIN CATCH
            ;THROW
        END CATCH
    END
    GO

    So it checks if it's an update.  If the Bit says it's not then the data will be inserted.  Otherwise, the data will be updated. 

    But the problem is that the @idSubsystem property can be freely inserted... I can just fill in 10 and it will be 10...   I want that the @idSubsystem property will be taken out of the supertype Subsystem. 

    I have searched for some time and tried to build my own trigger that gets the @idSubsystem as IDENT_CURRENT( 'Subsystem' )  out of the Subsystem table. 

    Here is the Trigger code:

     

    CREATE TRIGGER [dbo].[Control_Inserted_Engine] ON [dbo].Engine
      AFTER INSERT, UPDATE
    AS
       -- This is your primary key.
       DECLARE @idEngine INT = 0

            -- This is the max id from Subsystem.
            DECLARE @idSubsystem INT = IDENT_CURRENT( 'Subsystem' )

       -- Looping variable.
       DECLARE @i INT = 0

       -- Count of rows affected for looping over
       DECLARE @count INT

       -- These are your old variables.
       DECLARE @Engine_Part_Number        INT
       DECLARE @Engine_Revision_Number VARCHAR(50)
       DECLARE @Engine_Name            VARCHAR(50)
       DECLARE @Engine_Description        VARCHAR(50)
       DECLARE @update                    BIT

      BEGIN
       SET NOCOUNT ON ;

       -- Get count of affected rows
       SELECT @Count = Count(idEngine)
       FROM  inserted

       -- Loop over rows affected
       WHILE @i < @count
        BEGIN
          -- Get the next idEngine
          SELECT TOP 1
            @idEngine = idEngine
          FROM  inserted
          WHERE idEngine > @idEngine
          ORDER BY idEngine ASC

          -- Populate values for the current row
          SELECT @idSubsystem = idSubsystem,
            @Engine_Part_Number = Engine_Part_Number,
                            @Engine_Revision_Number = Engine_Revision_Number,
            @Engine_Name = Engine_Name,
            @Engine_Description = Engine_Description
          FROM  Inserted
          WHERE idEngine = @idEngine

          -- Run your stored procedure
          EXEC sp_add_or_update_engine @idEngine, @idSubsystem, @Engine_Part_Number,
           @Engine_Revision_Number, @Engine_Name, @Engine_Description, @update

                    EXEC sp_add_or_update_subsystem @idSubsystem

          -- Set up next iteration
          SET @i = @i + 1
        END
      END
    GO

    But when I execute the stored procedure i  get instant a error:


    exec sp_add_or_update_engine NULL, 1, 1, 'r10', 'Ferrari v8', 'Test Ferrari Engine' ,0

    Result:

    (0 rows affected)
    Msg 544, Level 16, State 1, Procedure sp_add_or_update_subsystem, Line 7 [Batch Start Line 15]
    Cannot insert explicit value for identity column in table 'SUBSYSTEM' when IDENTITY_INSERT is set to OFF.

    So how can I insert any data inside the Engine that also will insert the correct idSubsystem inside the engine that comes from Subsystem ??

    Greetingz,

  • I assume that changing the stored procedure is an option?  If so, get rid of the @idSubsytem parameter and put an INSERT INTO Subsystem DEFAULT VALUES statement (I'm doing this from memory, so check the syntax) into the stored procedure with an OUTPUT clause that will capture the value that you inserted.  Use that captured value in your INSERT INTO Engine statement instead of the parameter value.

    If changing the overall design of the database is also an option, get rid of the Subsystem table and use a Sequence object instead.

    John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply