October 8, 2016 at 1:29 pm
In t-sql 2012, I want like to update 3 tables at the same time. I would like the lockid value be set in a table called lock by the unique proimary key identifier. I would then like to pass the lockid value to the tables called locker and lockercombination. The rows for the locker and lockercombination would be obtain from other tables by doing joins. Listed below are the ddls for the 3 tables I am referring to. Would you show me the sql on how to accomplish this goal?
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[locationID] [int] NULL,
[grade] [varchar](4) NULL,
[reserved] [bit] NULL,
[lockID] [int] NULL,
[share] [bit] NULL,
[lockType] [varchar](3) NULL,
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
(
[lockerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_Lock]
GO
ALTER TABLE [dbo].[Locker] WITH CHECK ADD CONSTRAINT [FK_Locker_LockerLocation] FOREIGN KEY([locationID])
REFERENCES [dbo].[LockerLocation] ([locationID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_LockerLocation]
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_School]
GO
--------------
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[comboSeq] [tinyint] NOT NULL,
CONSTRAINT [PK_Lock] PRIMARY KEY NONCLUSTERED
(
[lockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Lock] WITH NOCHECK ADD CONSTRAINT [FK_Lock_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Lock] CHECK CONSTRAINT [FK_Lock_School]
GO
-------------------
CREATE TABLE [dbo].[LockCombination](
[comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lockID] [int] NOT NULL,
[seq] [tinyint] NOT NULL,
[combo] [varchar](8) NOT NULL,
CONSTRAINT [PK_LockCombination] PRIMARY KEY NONCLUSTERED
(
[comboID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
October 9, 2016 at 6:02 pm
Didn't we already have this discussion? Is this not a repetition of this earlier thread?
I am under the impression that either you are describing something incorrectly or your design is flawed. I pointed that out in the previous post and you never answered. This whole thing sounds like a variation on a lease. A person "leases" a locker for a term, and has a lock which has a combination. If the locks can be removed and have serial numbers, assigning locks to lockers is child's play.
What's the problem? Why would you need to update all 3 tables? sounds like improper normalization - a piece of information belongs in exactly ONE table. How is this scenario different?
October 10, 2016 at 11:16 am
If you are inserting one "lock" row at a time, you can do the insert and then the latest lockid would be available with "scope_identity()". If you are not inserting one "lock" row at a time but wanting to update all "locker" rows and "lockercombination" rows, then you'll have to figure out how rows in the "lock" are related to the rows in "locker" and "lockercombination", then do an update using the columns that relate to determine which lockid from table "lock" that you'll store in "locker" and "lockercombination".
October 10, 2016 at 11:36 am
What is supposed to be doing the insert? A person? An application?
One idea is to have a trigger on the table that takes the inserted ID value and adds them as FKs to the related tables. Still, how do you fill the remainder of the fields in the FK tables? It comes back to the design of how you are inserting data into your tables. Seeing sample data and a data flow outline would help.
----------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply