February 28, 2010 at 1:19 pm
I'm creating triggers that create/update a record in a history table as records are added to a master table. If a new record is added to the master, a history record needs to be created. For later updates, I want to capture the Identity of the new record in the history table and store it in a column in the master table. The idea is that the Update trigger will use that value to explicitly identify the record that needs to be updated. I've got the following INSERT query, but how would I capture the Identity value of the newly created record and then execute an UPDATE to add it to the master table?
(Obviously it would be changed from TrailerInventory to inserted)
INSERT INTO TrailerInventoryHistory (DOTNumber, IsInFleet, StartDate, ShowNumber, DepartmentId, UsageStatus)
(SELECT DOTNumber, IsInFleet, GetDate() As StartDate, CurrentShowNumber, CurrentDepartmentId, CurrentUsageStatus FROM TrailerInventory)
USE [TrailerManagement]
GO
/****** Object: Table [dbo].[TrailerInventory] Script Date: 02/28/2010 15:15:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TrailerInventory](
[DOTNumber] [int] NOT NULL,
[OwnerId] [int] NOT NULL,
[Type] [int] NULL,
[IsInFleet] [bit] NOT NULL,
[BranchId] [int] NULL,
[CurrentInventoryHistoryRecordId] [int] NULL,
[CurrentShowNumber] [nvarchar](8) NULL,
[CurrentDepartmentId] [int] NULL,
[CurrentUsageStatus] [nvarchar](15) NULL,
CONSTRAINT [PK_TrailerInventory] PRIMARY KEY CLUSTERED
(
[DOTNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [TrailerManagement]
GO
/****** Object: Table [dbo].[TrailerInventoryHistory] Script Date: 02/28/2010 15:15:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TrailerInventoryHistory](
[Id] [int] IDENTITY(1000,1) NOT NULL,
[DOTNumber] [int] NOT NULL,
[IsInFleet] [bit] NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[ShowNumber] [nvarchar](8) NULL,
[DepartmentId] [int] NULL,
[UsageStatus] [nvarchar](15) NULL,
CONSTRAINT [PK_TrailerInventoryHistory] 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]
GO
February 28, 2010 at 4:36 pm
I'm creating triggers that create/update a record in a history table as records are added to a master table. If a new record is added to the master, a history record needs to be created
Might I suggest that you look at using an OUTPUT clause in the T_SQL statement that adds records to the master table. Using that will eliminate the need for a trigger to write to the history table and can be included in a transaction and as such either commited or rolled back if an error is incurred when writing to the either table.
February 28, 2010 at 5:36 pm
bitbucket-25253 (2/28/2010)
I'm creating triggers that create/update a record in a history table as records are added to a master table. If a new record is added to the master, a history record needs to be created
Might I suggest that you look at using an OUTPUT clause in the T_SQL statement that adds records to the master table. Using that will eliminate the need for a trigger to write to the history table and can be included in a transaction and as such either commited or rolled back if an error is incurred when writing to the either table.
The records are being added via an Access ADP front end working directly on the table. The purpose of the trigger is log the changes to the history table. I could have sworn that I read somewhere that you can grab a value in one part of a SQL Statement and then turn around and use it in the same statement.
March 1, 2010 at 6:55 am
Sorry to be so long in getting back to you
Think you might be referring to IDENT_CURRENT - check it out in Books On Line or
http://technet.microsoft.com/en-us/library/aa933217(SQL.80).aspx
March 1, 2010 at 7:39 am
I understand that IDENT_CURRENT will retrieve the identity value, but how do I incorporate that into a single SQL statement that INSERTS the child record, grabs the IDENTITY and then UPDATES the parent record?
If it has to be done with a stored procedure, how do I call the stored procedure in a trigger and pass to the sp the entire INSERTED recordset for processing?
March 1, 2010 at 10:00 am
Warning - this code has not been tested extensively -- so you must do so before placing into production.
CREATE Procedure Dbo.TrInven
@DOTNumber INT,
@OwnerId INT,
@IsInF BIT
AS
DECLARE @id INT
INSERT INTO [dbo].[TrailerInventory](DOTNumber, OwnerID, IsInFLEET )
OUTPUT INSERTED.DOTNumber, INSERTED.IsInFLEET,GETDATE()
INTO [dbo].[TrailerInventoryHistory](DOTNumber,IsInFleet,StartDate)
VALUES (@DotNumber,@OwnerID,@IsInF)
SET @id = (SELECT IDENT_CURRENT('[dbo].[TrailerInventoryHistory]'))
UPDATE [dbo].[TrailerInventory] SET [CurrentInventoryHistoryRecordId]=@id
WHERE [dbo].[TrailerInventory].DOTNumber = @DOTNumber
March 1, 2010 at 12:29 pm
bitbucket-25253 (3/1/2010)
Warning - this code has not been tested extensively -- so you must do so before placing into production.
CREATE Procedure Dbo.TrInven@DOTNumber INT,
@OwnerId INT,
@IsInF BIT
AS
DECLARE @id INT
INSERT INTO [dbo].[TrailerInventory](DOTNumber, OwnerID, IsInFLEET )
OUTPUT INSERTED.DOTNumber, INSERTED.IsInFLEET,GETDATE()
INTO [dbo].[TrailerInventoryHistory](DOTNumber,IsInFleet,StartDate)
VALUES (@DotNumber,@OwnerID,@IsInF)
SET @id = (SELECT IDENT_CURRENT('[dbo].[TrailerInventoryHistory]'))
UPDATE [dbo].[TrailerInventory] SET [CurrentInventoryHistoryRecordId]=@id
WHERE [dbo].[TrailerInventory].DOTNumber = @DOTNumber
Please keep in mind that this would be my first experience with this scenario.
1) We're creating a stored procedure to handle the update. Right?
2) Stored procedures DO have access to the 'deleted' and 'inserted' record sets?
(I was thinking that they didn't)
3) How do I call this from a trigger? Simply EXECUTE TrInven? What's the point of declaring the three input parameters if the values are available via inserted?
March 1, 2010 at 3:07 pm
1) We're creating a stored procedure to handle the update. Right?
The Stored Procedure (Sp) will add a new row (INSERT) to the TrailerInventory table. It could be rewritten to UPDATE an existing row if so desired.
2) Stored procedures DO have access to the 'deleted' and 'inserted' record sets?
It is the OUTPUT clause that allows us to access the "inserted" and "deleted" items. Without an OUTPUT clause - no access to "inserted" / "deleted" values.
3) How do I call this from a trigger? Simply EXECUTE TrInven? What's the point of declaring the three input parameters if the values are available via inserted?
The 3 values (for brevity sake that is all I elected to use in my example) are those values which are to be inserted into the [trailerInventory] table. Of course to use in the real world you would pass additional values for example the values for type, branchid etc.
If I understood you properly there is NO NEED for a trigger. That is the trigger was to be utilized to recover the [dbo].[TrailerInventoryHistory] identity value and to UPDATE the corresponding field in the ].[TrailerInventory], that is the
[CurrentInventoryHistoryRecordId]
Again I hope you have a "sandbox" / developers database to test this, then test again and again and if acceptable, understand what it does and why it does what it does. The output clause is a very powerful aspect in T-SQL not only minimizing the need for Triggers, but for creating "Audit" tables, (who changed what and when)
Now if this does not clarify things enough please post again.
March 3, 2010 at 8:50 am
Below is the source for a trigger solution.
I have made two assumptions:
1. The value of column DOTNumber in table TrailerInventory cannot change.
2. All updates to column CurrentInventoryHistoryRecordId in table TrailerInventory are made only by triggers.
Of special note is the logic needed to not take any action when there is a recursive trigger execution for the case when the trigger on tables TrailerInventoryHistory updates the TrailerInventory table, causing the TrailerInventory after update trigger to execute.
Other suggested solutions require that every insert, update or merge statement on table TrailerInventory also include logic to populate the TrailerInventoryHistory, which is subject to human error of not including the logic.
CREATE TRIGGER [dbo].[TrailerInventory_tia900]
ON [dbo].[TrailerInventory]
AFTER INSERT -- order 900
AS
SET NOCOUNT ON;
SETXACT_ABORT ON;
IF 0 = (select count(*) from inserted) RETURN;
INSERT INTO dbo.TrailerInventoryHistory
( DOTNumber, IsInFleet, ShowNumber
, DepartmentId, UsageStatus
, StartDate, EndDate)
SELECT DOTNumber, IsInFleet, CurrentShowNumber
, CurrentDepartmentId, CurrentUsageStatus
, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP
FROM inserted;
GO
CREATE TRIGGER [dbo].[TrailerInventory_tua900]
ON [dbo].[TrailerInventory]
AFTER update -- order 900
AS
SET NOCOUNT ON;
SETXACT_ABORT ON;
IF 0 = (select count(*) from inserted) RETURN;
IF(SELECTcount(*)
FROMinserted
joindeleted
on inserted.DOTNumber = deleted.DOTNumber
)
<>(select count(*) from inserted)
BEGIN
RAISERROR('TrailerInventory column DOTNumber is not updatable.',1,16)
ROLLBACK
RETURN
END
INSERT INTO dbo.TrailerInventoryHistory
( DOTNumber, IsInFleet, ShowNumber
, DepartmentId, UsageStatus
, StartDate, EndDate)
SELECT inserted.DOTNumber, inserted.IsInFleet, inserted.CurrentShowNumber
, inserted.CurrentDepartmentId, inserted.CurrentUsageStatus
, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP
FROMinserted
joindeleted
on inserted.DOTNumber = deleted.DOTNumber
-- Ignore update to CurrentInventoryHistoryRecordId
andinserted.CurrentInventoryHistoryRecordId = deleted.CurrentInventoryHistoryRecordId
GO
CREATE TRIGGER [dbo].[TrailerInventoryHistory_tia900]
ON [dbo].TrailerInventoryHistory
AFTER INSERT -- order 900
AS
SET NOCOUNT ON;
SETXACT_ABORT ON;
IF 0 = (SELECT COUNT(*) FROM inserted) RETURN;
UPDATEX
SETX.CurrentInventoryHistoryRecordId = inserted.id
FROM[dbo].[TrailerInventory] AS X
JOINinserted
ON inserted.DOTNumber = X.DOTNumber
WHERE( X.CurrentInventoryHistoryRecordId <> inserted.ID
OR X.CurrentInventoryHistoryRecordId IS NULL )
GO
CREATE TRIGGER [dbo].[TrailerInventoryHistory_tua_001]
ON [dbo].TrailerInventoryHistory
after update
AS
SET NOCOUNT ON;
SETXACT_ABORT ON;
IF 0 <> (select count(*) from inserted)
BEGIN
RAISERROR('TrailerInventoryHistory cannot be updated.',1,16)
ROLLBACK
RETURN
END
GO
SQL = Scarcely Qualifies as a Language
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply