October 31, 2018 at 5:45 am
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.
October 31, 2018 at 6:23 am
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
October 31, 2018 at 7:28 am
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,
October 31, 2018 at 8:27 am
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