Tables [dbo].[MilitaryWeight]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)3485
Created10:12:49 PM Thursday, December 28, 2006
Last Modified7:33:37 PM Thursday, August 29, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Primary Key PK_MilitaryWeight: MilitaryWeightIDMilitaryWeightIDint4
No
1 - 1
Foreign Keys FK_MilitaryWeight_Orders: [dbo].[Orders].OrdersFIDIndexes IX_MilitaryWeight_OrdersFID: OrdersFIDOrdersFIDint4
No
Datedatetime8
Yes
TruckNumbervarchar(32)32
Yes
OriginalGrossint4
Yes
OriginalTareint4
Yes
OriginalNetint4
Yes
OriginalProGearint4
Yes
ReweighDatedatetime8
Yes
ReweighNumbervarchar(8)8
Yes
ReweighGrossint4
Yes
ReweighTareint4
Yes
ReweighNetint4
Yes
ReweighProGearint4
Yes
Foreign Keys FK_MilitaryWeight_OrderAuditInfo: [dbo].[OrderAuditInfo].OrderAuditInfoFIDIndexes IX_MilitaryWeight_OrderAuditInfoFID: OrderAuditInfoFIDOrderAuditInfoFIDbigint8
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_MilitaryWeight: MilitaryWeightIDPK_MilitaryWeightMilitaryWeightID
Yes
80
IX_MilitaryWeight_OrderAuditInfoFIDOrderAuditInfoFID
IX_MilitaryWeight_OrdersFIDOrdersFID
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
MilitaryWeightDeletedAudit
Yes
Yes
After Delete
MilitaryWeightInsertedAudit
Yes
Yes
After Insert
MilitaryWeightTaskFieldChanged
Yes
Yes
After Insert Update
MilitaryWeightUpdatedAudit
Yes
Yes
After Update
Foreign Keys Foreign Keys
NameColumns
FK_MilitaryWeight_OrderAuditInfoOrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_MilitaryWeight_OrdersOrdersFID->[dbo].[Orders].[PriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[MilitaryWeight]
(
[MilitaryWeightID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[Date] [datetime] NULL,
[TruckNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalGross] [int] NULL,
[OriginalTare] [int] NULL,
[OriginalNet] [int] NULL,
[OriginalProGear] [int] NULL,
[ReweighDate] [datetime] NULL,
[ReweighNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReweighGross] [int] NULL,
[ReweighTare] [int] NULL,
[ReweighNet] [int] NULL,
[ReweighProGear] [int] NULL,
[OrderAuditInfoFID] [bigint] NULL
) ON [PRIMARY]
GO

-- Create a trigger that records audit information when a MilitaryWeight record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
CREATE trigger [dbo].[MilitaryWeightDeletedAudit] on [dbo].[MilitaryWeight] after delete
as
    set nocount on

    declare @theAuditFields table
    (
        MilitaryWeightFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )
    insert into @theAuditFields
    (
        MilitaryWeightFID,
        FieldName,
        [Value]
    )
    select deleted.MilitaryWeightID, 'OriginalGross', null
    from deleted
    where isnull( deleted.OriginalGross, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'OriginalNet', null
    from deleted
    where isnull( deleted.OriginalNet, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'ReweighGross', null
    from deleted
    where isnull( deleted.ReweighGross, -1 ) != -1
    union all
    select deleted.MilitaryWeightID, 'ReweighNet', null
    from deleted
    where isnull( deleted.ReweighNet, -1 ) != -1

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from deleted where OrderAuditInfoFID is null )
    begin
        select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
        OrderFID = deleted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =
            case
                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                else OrderAuditInfo.UpdateSource
            end
    from @theAuditFields theAuditFields
    inner join deleted on theAuditFields.MilitaryWeightFID = deleted.MilitaryWeightID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

    -- we're done with the metadata, so clean it up.
    update MilitaryWeight
    set OrderAuditInfoFID = NULL
    from deleted
    inner join MilitaryWeight on deleted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
GO

-- Create a trigger that records audit information when a MilitaryWeight record is inserted.
-- Only records audit information for fields that have a value.
CREATE trigger [dbo].[MilitaryWeightInsertedAudit] on [dbo].[MilitaryWeight] after insert
as
set nocount on
    declare @theAuditFields table
    (
        MilitaryWeightFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        MilitaryWeightFID,
        FieldName,
        [Value]
    )
    select inserted.MilitaryWeightID, 'OriginalGross', convert(varchar,inserted.OriginalGross)
    from inserted
    where update( OriginalGross )
    union all
    select inserted.MilitaryWeightID, 'OriginalNet', convert(varchar,inserted.OriginalNet)
    from inserted
    where update( OriginalNet )
    union all
    select inserted.MilitaryWeightID, 'ReweighGross', convert(varchar,inserted.ReweighGross)
    from inserted
    where update( ReweighGross )
    union all
    select inserted.MilitaryWeightID, 'ReweighNet', convert(varchar,inserted.ReweighNet)
    from inserted
    where update( ReweighNet )    
    union all
    select inserted.MilitaryWeightID, 'OriginalDate', convert(varchar, inserted.[Date], 126 )
    from inserted
    where update( [Date] )  
    union all
    select inserted.MilitaryWeightID, 'ReweighDate', convert(varchar, inserted.ReweighDate, 126 )
    from inserted
    where update( [ReweighDate] )
    
    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
    begin
        select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
    OrderFID = inserted.OrdersFID,
    ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
    ChangedOn = @theChangedOn,
    OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
    ChangedTo = Value,
    ChangedIn =
        case
            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
            else OrderAuditInfo.UpdateSource
        end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.MilitaryWeightFID = inserted.MilitaryWeightID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
    where theAuditFields.[Value] is not null

    -- we're done with the metadata, so clean it up.
    update MilitaryWeight
    set OrderAuditInfoFID = NULL
    from Inserted
    inner join MilitaryWeight on Inserted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
GO

CREATE trigger [dbo].[MilitaryWeightTaskFieldChanged] on [dbo].[MilitaryWeight]
after insert, update
as
set nocount on
    if( update( [Date] ) or update( ReweighDate ) )
    begin
        update ToDo set ToDo.DueDate =
            case DependencyDate.FieldName
                when 'Date' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.Date )
                when 'ReweighDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ReweighDate )
            end
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'MilitaryWeight'
    end
GO

-- Create a trigger that records audit information when a MilitaryWeight record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[MilitaryWeightUpdatedAudit] on [dbo].[MilitaryWeight] after update
as
set nocount on
    set nocount on

    -- When we clear the OrderAuditInfoFID field, no auditing is needed.
    if( update( OrderAuditInfoFID ) and not exists ( select OrderAuditInfoFID from inserted where OrderAuditInfoFID is not null ) )
    begin
        return  
    end
    else
    begin
        declare @theAuditFields table
        (
            MilitaryWeightFID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        insert into @theAuditFields
        (
            MilitaryWeightFID,
            FieldName,
            [Value]
        )
        select inserted.MilitaryWeightID, 'OriginalGross', convert(varchar,inserted.OriginalGross)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.OriginalGross, -1 ) != isnull( deleted.OriginalGross, -1 )
        where update( OriginalGross )
        union all
        select inserted.MilitaryWeightID, 'OriginalNet', convert(varchar,inserted.OriginalNet)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.OriginalNet, -1 ) != isnull( deleted.OriginalNet, -1 )
        where update( OriginalNet )
        union all
        select inserted.MilitaryWeightID, 'ReweighGross', convert(varchar,inserted.ReweighGross)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.ReweighGross, -1 ) != isnull( deleted.ReweighGross, -1 )
        where update( ReweighGross )
        union all
        select inserted.MilitaryWeightID, 'ReweighNet', convert(varchar,inserted.ReweighNet)
        from inserted
        inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
            isnull( inserted.ReweighNet, -1 ) != isnull( deleted.ReweighNet, -1 )
        where update( ReweighNet )
        union all
            select inserted.MilitaryWeightID, 'OriginalDate', convert( varchar, inserted.[Date], 126 )
            from inserted
            inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
                1 = dbo.DateFieldChanged( deleted.[Date], inserted.[Date] )
            where update ([Date])
        union all
            select inserted.MilitaryWeightID, 'ReweighDate', convert( varchar, inserted.ReweighDate, 126 )
            from inserted
            inner join deleted on inserted.MilitaryWeightID = deleted.MilitaryWeightID and
                1 = dbo.DateFieldChanged( deleted.ReweighDate, inserted.ReweighDate )
            where update (ReweighDate)

        declare @theChangedOn datetime
        set @theChangedOn = dbo.GetMssDateTime()

        declare @theExtAppUser int
        if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
        begin
            select @theExtAppUser = SysUser.SysUserID
            from dbo.Sysuser
            where SysUser.FIRSTNAME = 'External' and
            SysUser.LASTNAME = 'Application'
        end

        insert into OrderHistory
        (
            OrderFID,
            ChangedBy,
            ChangedOn,
            OrderHistoryFieldFID,
            ChangedTo,
            ChangedIn
        )
        select
        OrderFID = inserted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =
            case
                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                else OrderAuditInfo.UpdateSource
            end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.MilitaryWeightFID = inserted.MilitaryWeightID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

        -- we're done with the metadata, so clean it up.        
        update MilitaryWeight
        set OrderAuditInfoFID = NULL
        from Inserted
        inner join MilitaryWeight on Inserted.MilitaryWeightID = MilitaryWeight.MilitaryWeightID

        delete OrderAuditInfo
        from inserted
        inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [PK_MilitaryWeight] PRIMARY KEY NONCLUSTERED  ([MilitaryWeightID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryWeight_OrderAuditInfoFID] ON [dbo].[MilitaryWeight] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryWeight_OrdersFID] ON [dbo].[MilitaryWeight] ([OrdersFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [FK_MilitaryWeight_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[MilitaryWeight] ADD CONSTRAINT [FK_MilitaryWeight_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT INSERT ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT DELETE ON  [dbo].[MilitaryWeight] TO [MssExec]
GRANT UPDATE ON  [dbo].[MilitaryWeight] TO [MssExec]
GO
Uses
Used By