/****** Object: Table [WMS].[Laneway] Script Date: 2022/6/22 8:39:31 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Laneway](
[Id] [uniqueidentifier] NOT NULL,
[CreationTime] [datetime2](7) NOT NULL,
[CreatorId] [uniqueidentifier] NULL,
[LastModificationTime] [datetime2](7) NULL,
[LastModifierId] [uniqueidentifier] NULL,
[XCode] [nvarchar](50) NULL,
[XName] [nvarchar](50) NULL,
[XArea] [nvarchar](50) NULL,
[WarehouseId] [uniqueidentifier] NOT NULL,
[HasDeviceError] [nvarchar](1) NULL,
[DisallowWcsToClearError] [nvarchar](1) NULL,
[IsDoubleDeep] [nvarchar](1) NULL,
[IsDoubleWide] [nvarchar](1) NULL,
[AllcationOrder] [int] NOT NULL,
[TotalLocationCount] [int] NOT NULL,
[LoadLocationCount] [int] NOT NULL,
[LeftLocationCount] [int] NOT NULL,
[UsageRate] [decimal](18, 2) NOT NULL,
[LoadRate] [decimal](18, 2) NOT NULL,
[IsFull] [nvarchar](1) NULL,
[UsageRateUpper] [decimal](18, 2) NULL,
[Creator] [nvarchar](50) NULL,
[LastModifier] [nvarchar](50) NULL,
[LanwayNo] [int] NOT NULL,
CONSTRAINT [PK_Laneway] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Laneway] ADD DEFAULT ((0)) FOR [LanwayNo]
CREATE TABLE [Lanewaytemp](
[Id] [uniqueidentifier] NOT NULL,
[CreationTime] [datetime2](7) NOT NULL,
[CreatorId] [uniqueidentifier] NULL,
[LastModificationTime] [datetime2](7) NULL,
[LastModifierId] [uniqueidentifier] NULL,
[XCode] [nvarchar](50) NULL,
[XName] [nvarchar](50) NULL,
[XArea] [nvarchar](50) NULL,
[WarehouseId] [uniqueidentifier] NOT NULL,
[HasDeviceError] [nvarchar](1) NULL,
[DisallowWcsToClearError] [nvarchar](1) NULL,
[IsDoubleDeep] [nvarchar](1) NULL,
[IsDoubleWide] [nvarchar](1) NULL,
[AllcationOrder] [int] NOT NULL,
[TotalLocationCount] [int] NOT NULL,
[LoadLocationCount] [int] NOT NULL,
[LeftLocationCount] [int] NOT NULL,
[UsageRate] [decimal](18, 2) NOT NULL,
[LoadRate] [decimal](18, 2) NOT NULL,
[IsFull] [nvarchar](1) NULL,
[UsageRateUpper] [decimal](18, 2) NULL,
[Creator] [nvarchar](50) NULL,
[LastModifier] [nvarchar](50) NULL,
[LanwayNo] [int] NOT NULL,
[FLAG] char,
[SUETIME] datetime,
constraint pkLantempID primary key(Id)
);
create trigger tes1
on Laneway
after update
as
if not exists(select 1 from Lanewaytemp ,inserted as a where a.Id = Lanewaytemp.Id)
begin
insert into Lanewaytemp ([Id], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [XCode], [XName], [XArea], [WarehouseId], [HasDeviceError], [DisallowWcsToClearError], [IsDoubleDeep], [IsDoubleWide], [AllcationOrder],[TotalLocationCount], [LoadLocationCount], [LeftLocationCount], [UsageRate], [LoadRate], [IsFull], [UsageRateUpper], [Creator], [LastModifier], [LanwayNo]) select * from inserted
update Lanewaytemp set FLAG = '0',SUETIME=GETDATE() from Lanewaytemp,inserted as a where a.Id = Lanewaytemp.Id
end
else
begin
update Lanewaytemp set FLAG = '1',SUETIME=GETDATE() from Lanewaytemp,inserted as a where a.Id = Lanewaytemp.Id
end;
INSERT [Laneway] ([Id], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [XCode], [XName], [XArea], [WarehouseId], [HasDeviceError], [DisallowWcsToClearError], [IsDoubleDeep], [IsDoubleWide], [AllcationOrder], [TotalLocationCount], [LoadLocationCount], [LeftLocationCount], [UsageRate], [LoadRate], [IsFull], [UsageRateUpper], [Creator], [LastModifier], [LanwayNo]) VALUES (N'3ce1a760-ab38-4a8d-b795-00a449c88a29', CAST(N'2021-03-31T11:18:37.2530000' AS DateTime2), NULL, CAST(N'2022-02-23T13:50:43.0970000' AS DateTime2), N'ff39b1b9-c2b1-fb87-bc6e-3a011b588340', N'Lan04', N'巷道04', N'A0A88ADA-577B-4781-AA3A-EE2FAA9B5CE8', N'5652c9d8-0c29-4afe-bd82-ee2fa2c9bb04', N'Y', N'N', N'Y', N'N', 0, 1288, 0, 0, CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'N', CAST(0.00 AS Decimal(18, 2)), N'管理员', N'仓储经理', 4)
INSERT [Laneway] ([Id], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [XCode], [XName], [XArea], [WarehouseId], [HasDeviceError], [DisallowWcsToClearError], [IsDoubleDeep], [IsDoubleWide], [AllcationOrder], [TotalLocationCount], [LoadLocationCount], [LeftLocationCount], [UsageRate], [LoadRate], [IsFull], [UsageRateUpper], [Creator], [LastModifier], [LanwayNo]) VALUES (N'68603957-be91-4ba5-875b-037dedbfc43c', CAST(N'2021-03-31T11:20:57.7400000' AS DateTime2), NULL, CAST(N'2022-03-03T15:52:23.3330000' AS DateTime2), N'ff39b1b9-c2b1-fb87-bc6e-3a011b588340', N'Lan03', N'巷道03', N'A0A88ADA-577B-4781-AA3A-EE2FAA9B5CE8', N'5652c9d8-0c29-4afe-bd82-ee2fa2c9bb04', N'Y', N'N', N'Y', N'N', 0, 1288, 0, 0, CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'N', CAST(0.00 AS Decimal(18, 2)), N'管理员', N'仓储经理', 3)
INSERT [Laneway] ([Id], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [XCode], [XName], [XArea], [WarehouseId], [HasDeviceError], [DisallowWcsToClearError], [IsDoubleDeep], [IsDoubleWide], [AllcationOrder], [TotalLocationCount], [LoadLocationCount], [LeftLocationCount], [UsageRate], [LoadRate], [IsFull], [UsageRateUpper], [Creator], [LastModifier], [LanwayNo]) VALUES (N'b929f487-731e-4beb-ada9-d8639a867a5d', CAST(N'2021-03-31T11:20:57.7400000' AS DateTime2), NULL, CAST(N'2022-02-23T13:50:41.5330000' AS DateTime2), N'ff39b1b9-c2b1-fb87-bc6e-3a011b588340', N'Lan02', N'巷道02', N'A0A88ADA-577B-4781-AA3A-EE2FAA9B5CE8', N'5652c9d8-0c29-4afe-bd82-ee2fa2c9bb04', N'Y', N'N', N'Y', N'N', 0, 1288, 0, 0, CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'N', CAST(0.00 AS Decimal(18, 2)), N'管理员', N'仓储经理', 2)
INSERT [Laneway] ([Id], [CreationTime], [CreatorId], [LastModificationTime], [LastModifierId], [XCode], [XName], [XArea], [WarehouseId], [HasDeviceError], [DisallowWcsToClearError], [IsDoubleDeep], [IsDoubleWide], [AllcationOrder], [TotalLocationCount], [LoadLocationCount], [LeftLocationCount], [UsageRate], [LoadRate], [IsFull], [UsageRateUpper], [Creator], [LastModifier], [LanwayNo]) VALUES (N'9bf7d0b9-939c-40da-8ce4-df48e61119e0', CAST(N'2021-03-31T11:20:57.7300000' AS DateTime2), NULL, CAST(N'2022-02-23T13:50:40.8300000' AS DateTime2), N'ff39b1b9-c2b1-fb87-bc6e-3a011b588340', N'Lan01', N'巷道01', N'A0A88ADA-577B-4781-AA3A-EE2FAA9B5CE8', N'5652c9d8-0c29-4afe-bd82-ee2fa2c9bb04', N'Y', N'N', N'Y', N'N', 0, 1288, 0, 0, CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'N', CAST(0.00 AS Decimal(18, 2)), N'管理员', N'仓储经理', 1)