December 5, 2012 at 9:49 am
I' trying to create a trigger that creates a record after an update to an existing record (for a log) and takes the next record in the tblTransactions table as below. I'm using SQL 2008. Am I on the right track here... I know it's not compiling:
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
INSERT INTO tblTransactions
(tblTransactions.@@IDENTITY,
tblTransactions.Unit_Price,
tblTransactions.Software_Description,
tblTransactions.PurchaseCostCenter,
tblTransactions.HeatTicketNumber,
tblTransactions.EndUserFirstName,
tblTransactions.EndUserLastName,
tblTransactions.LAN_ID)
SELECT
INSERTED.Transaction_Number,
INSERTED.Unit_Price,
INSERTED.Software_Description,
INSERTED.PurchaseCostCenter,
INSERTED.HeatTicketNumber,
INSERTED.EndUserFirstName,
INSERTED.EndUserLastName,
INSERTED.LAN_ID
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
END
December 5, 2012 at 9:57 am
briancampbellmcad (12/5/2012)
(tblTransactions.@@IDENTITY
What's the definition of the tblTransactions table - I'm assuming you don't actually have a field in the table called @@IDENTITY?
And if I'm reading your question right, you want to insert a record into the same table that the trigger is on?
Thanks
December 5, 2012 at 9:59 am
Here's my DDL for my table:
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTransactions](
[PO_Number] [varchar](50) NULL,
[Buyer_Initial] [varchar](50) NULL,
[Quantity] [int] NULL,
[Unit_Price] [money] NULL,
[Software_Description] [varchar](100) NULL,
[AllocationAccount] [varchar](50) NULL,
[PurchaseAccount] [varchar](50) NULL,
[HeatTicketNumber] [varchar](50) NULL,
[PurchaseCostCenter] [varchar](25) NULL,
[PO_Date] [date] NULL,
[Transaction_Date] [date] NULL,
[Transaction_Number] [int] IDENTITY(18000,1) NOT NULL,
[AllocationDate] [date] NULL,
[AllocatedYN] [varchar](10) NULL,
[EndUserFirstName] [varchar](100) NULL,
[EndUserMiddleName] [varchar](100) NULL,
[EndUserLastName] [varchar](100) NULL,
[LAN_ID] [varchar](50) NULL,
[EndUserLocation] [varchar](100) NULL,
[TermDate] [date] NULL,
[EmployeeStatus] [varchar](50) NULL,
[Notes] [varchar](255) NULL,
[LicenseAvailable] [varchar](3) NULL,
[Transaction_Type] [varchar](50) NULL,
[NextRenewalDate] [date] NULL,
[Last_Renewal_Date] [date] NULL,
[LastRenewalPO] [varchar](50) NULL,
[AllocationCostCenter] [varchar](50) NULL,
[SoftwareShortName] [varchar](10) NULL,
[PC_Name] [varchar](100) NULL,
[TransferedSoftware] [varchar](10) NULL,
[TransferToFName] [varchar](50) NULL,
[TransferToLName] [varchar](50) NULL,
[TransferToLANID] [varchar](25) NULL,
[Transfered] [varchar](5) NULL,
[OriginalTransactionNumber] [varchar](6) NULL,
[TransferToCostCenter] [varchar](10) NULL,
[CostCenter] [varchar](10) NULL,
CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
(
[Transaction_Number] 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
SET ANSI_PADDING OFF
GO
December 5, 2012 at 10:10 am
Thanks Brian,
Are you wanting to put the next Transaction_Number Identity value into the Transaction_Number field?
And by next, do you mean next free Identity value or Transaction_Number + 1?
Cheers
Gaz
December 5, 2012 at 10:32 am
I'm wanting the trigger to create a record and take the next number in the table's sequence.
December 5, 2012 at 12:20 pm
What is the logic in inserting a new record into tblTransactions when a record in tblTransactions is updated? Shouldn't this be logged to a different table?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 5, 2012 at 12:33 pm
I'm thinking now that tblTransactions.@@IDENTITY should actually be tblTransactions.OriginalTransactionNumber?
You can't have @@IDENTITY in the insert list and you also can't insert into Transaction_Number since it's an IDENTITY. (For the pedantics: yes, I know you could :satisfied:)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 5, 2012 at 12:51 pm
I'll study the second post, but to the first post - ignore my verbage about logs... it's not really a log... my transaction table gets records generated directly from purchases, but also in many indirect ways that the application does but a transaction still must be generated, and often by a trigger.
December 5, 2012 at 1:38 pm
So I can't have my trigger create a record to go in my tblTransactions and pick up the next available sequential number? I guess on my application side I could accomplish the same thing (?)
December 5, 2012 at 1:58 pm
briancampbellmcad (12/5/2012)
So I can't have my trigger create a record to go in my tblTransactions and pick up the next available sequential number? I guess on my application side I could accomplish the same thing (?)
You don't have to manually go into tblTransactions and get the next number, the IDENTITY does that for you. But you also can't insert a number in there. For example, a few changes to your trigger will work:
CREATE TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
INSERT INTO tblTransactions(
tblTransactions.OriginalTransactionNumber,
tblTransactions.Unit_Price,
tblTransactions.Software_Description,
tblTransactions.PurchaseCostCenter,
tblTransactions.HeatTicketNumber,
tblTransactions.EndUserFirstName,
tblTransactions.EndUserLastName,
tblTransactions.LAN_ID)
SELECT
INSERTED.Transaction_Number,
INSERTED.Unit_Price,
INSERTED.Software_Description,
INSERTED.PurchaseCostCenter,
INSERTED.HeatTicketNumber,
INSERTED.EndUserFirstName,
INSERTED.EndUserLastName,
INSERTED.LAN_ID
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
Notice I'm putting the Transaction_Number of the row that was updated into the OriginalTransactionNumber column of the new row.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply