Member Avatar for Afupi

Hello DaniWeb,
I have been working on a Maintenance Application. My company builds a specific type of system using specfic types of components. What I need to be able to do is keep track of which component is in which system given a certain event.

ComponentType Table: Ident, Type ( example1: 1, hard drive example2: Motherboard)

EventType Table: Ident, Type(example1: 1,installed example2:2, uninstalled)

System Table: Ident, SerialNum(example1: 1, 1234A example2:2, A4321)

Component Table: Ident, ComponentTypeIdent, SystemIdent, Slot(A system can have 2 HDD, but one motherboard for instance)(example1: 1, 1,1,2 example2:1,2,NULL(not in a system), NULL)

Event Table: Ident,EventTypeIdent,ComponentIdent,SystemIdent (example1: 1,1,1,1 example2 2,2,NULL(This should be set on postback of the component selection dropdown)

I need a query or stored procedure that will check a given EventTypeIdent, know if SystemIdent should be set to null(uninstall) or produce a list of Systems (install)that do not already have all slots filled for a given componentType and SystemIdent=NULL in Component Table.

Here is my latest attempt

 IF @EventTypeIdent=1
 SELECT Components.Ident
 FROM 
    Components 
 WHERE 
 Components.SystemIdent IS NULL 
 AND Components.ComponentTypeIdent=@ComponentTypeIdent
  • 2 Contributors
  • 3 Replies
  • 123 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Afupi

Dani AI

Generated

Good direction so far. , adding a PositionType to represent slot-type and capacity was the right move; ’s SystemSlot idea is a cleaner, normalized alternative if you want fixed physical slots instead of counting. Two practical goals now: (1) make the insert/update atomic and concurrency-safe, and (2) prevent installs that would exceed the per-system capacity for that position type.

Use a single stored procedure that wraps the whole operation in a transaction, inserts the Event and captures its id with an OUTPUT clause (or SCOPE_IDENTITY()), then applies the component update only after a concurrency-safe capacity check. A common pattern is to SELECT the current count with table hints to lock the relevant rows, compare to a MaxPerSystem value stored on PositionType, and THROW/ROLLBACK if full. For edits, treat Events as immutable (create correction events) or pass the original event into the proc and apply the inverse update + new update inside the same transaction.

Example skeleton (adapt to your column names):

CREATE PROCEDURE dbo.sp_LogComponentEvent
  @EventTypeId INT, @ComponentId INT, @SystemId INT = NULL,
  @PositionTypeId INT = NULL, @EventDate DATETIME = NULL, @Notes NVARCHAR(4000) = NULL,
  @NewEventId INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    BEGIN TRAN;

    DECLARE @Inserted TABLE (Id INT);
    INSERT INTO dbo.Events (EventTypeId, ComponentId, SystemId, EventDate, Notes)
    OUTPUT inserted.Id INTO @Inserted
    VALUES (@EventTypeId, @ComponentId, @SystemId, ISNULL(@EventDate, GETDATE()), @Notes);
    SELECT @NewEventId = Id FROM @Inserted;

    IF @EventTypeId = /* install id */
    BEGIN
      -- concurrency-safe capacity check
      SELECT @Current = COUNT(*) FROM dbo.Components WITH (UPDLOCK, HOLDLOCK)
        WHERE SystemId = @SystemId AND PositionTypeId = @PositionTypeId;
      SELECT @Max = MaxPerSystem FROM dbo.PositionTypes WHERE Id = @PositionTypeId;
      IF @Current >= @Max
      BEGIN
        ROLLBACK TRAN; THROW 50000, 'No available slot', 1;
      END
      UPDATE dbo.Components SET SystemId = @SystemId, PositionTypeId = @PositionTypeId WHERE Id = @ComponentId;
    END
    ELSE
      UPDATE dbo.Components SET SystemId = NULL, PositionTypeId = NULL WHERE Id = @ComponentId;

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRAN;
    THROW;
  END CATCH
END

Notes: prefer INSERT ... OUTPUT or SCOPE_IDENTITY() over @@IDENTITY (triggers can change @@IDENTITY). Use TRY/CATCH for reliable rollback. If you need strict integrity, model physical slots (SystemSlot) and enforce unique indexes instead of relying on counts. Microsoft refs: SCOPE_IDENTITY() and OUTPUT clause for details:

This pattern handles installs, uninstalls, and protects against race conditions while returning the new Event id for your FormView.

Recommended Answers

All 3 Replies

Member Avatar for BitBlt

Your request is a bit confusing. You appear to be asking for several different things, each of which should require it's own statement or stored proc. Your scenario needs further explanation.

For example, is your Event table used like a transaction to pass forward and trigger some action, or is it used to keep track of events that occur, like a log? It could have a big impact on design.

Also, do you have control over the database design? If so, you might consider creating one (or more?) child tables between System and Component to help resolve the installed/uninstalled issue, rather than using a nullable foreign key. So, you would have a System table, a SystemSlot table (child of System), a Component table and a SystemSlotComponent table (child of SystemSlot and Component). However, if you go that way you have to have Events that reference the specific Component, System and Slot, so you'd have to alter your Event table as well to include a Slot id.

Of course, that last bit is assuming a lot about what your app is supposed to do. Bottom line: please refine your explanation of what your app does, and the specific individual requirements. We will help if we can.

Member Avatar for Afupi

Apologies, as you can tell from my post, I am confused. Event is a log and a transaction. When an event is inserted I not only need to insert things like eventdate, componentid, and systemid into the event table, but also to change values in other tables.

Example:
Insert all event columns
If event type is install update component.systemid = event.systemid
If eventtype is uninstall component.systemid is NULL

The above psuedo code isn't the issue for me it is handling the knowing how many of each component type is in each system, and what the max number of each componenttype per system.

I guess it isn't the insert truly that is the issue but setting up the parameters and tables for the insertion and the affects throughout the db after the insertion. Come to think of it, I will need to do this for editting events as well. Encapsulation here I come.

Edited by Afupi
Member Avatar for Afupi

So I solved one problem by placing a PositionType Table and PositionTypeId column in my Components table. The only problem now is for my new Event to show up in the item template of my formview. I suppose this should be asked in the ASP.NET Forum. In case someone is following this thread maybe they can answer it though. I am unsure where to place the SELECT @NewID = @@Identity.

INSERT INTO [Events] ([EventTypeId], [ComponentId], [SystemId], [EventDate], [Notes]) VALUES (@EventTypeId, @ComponentId, @SystemId, @EventDate, @Notes);  SELECT @NewID = @@Identity

UPDATE [Components] SET [PositionTypeId]=@PositionTypeId,[SystemId]=@SystemId
WHERE Id=@ComponentId
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.