June 17, 2013 at 4:49 pm
When the user selects an Add New record - Table1 uses the Autocounter field to generate a PrimaryKey and creates the new record for data entry.
Table2 is part of a Rules Engine. It needs to have a one-to-one relationship of the primary keys.
The trigger must fire after the primary key (autocounter) is assigned to Table1. Use the AddNew record (or append) on Table2 - and include the same Primary key that is on Table1.
Note: Table1 never has a record deleted. It has an Active / Inactive field to determine what is live or historical.
Table2 will constantly have dozens of new columns added as the rules engine is developed. Table2 has metadata.
Table2 will not use an autocounter. Since the PK from the Table1 autocounter is unique, and in sequence, it will just be a 1:1 relationship between Table1 <--> Table2. At least that is the goal.
The front-end interface is Access 2010 using SQL Server Native 11.0 client and DSN-Less linked tables.
June 18, 2013 at 3:17 pm
Didn't get any suggestions. This worked fine.
First - created a table - Then create a trigger that after a new record is appended, it takes the latest new Primary key - then inserts a new record to the table and edits the field to receive the new primary key.
while this works - any suggestions would be great.
-- Create the receiving table This table only receives the Primary Key in first column
-- The trigger (below) fires after a new record is created. It takes the autocounter ID from the first column.
-- Then creates a new record in the R_AuditMaster and inserts the identical Primary Key.
-- The R_AuditMaster contains Metadata used for a Rule Engine - don't mix Data and rules
USE [RegulatoryDB]
GO
/****** Object: Table [dbo].[R_AuditMaster] Script Date: 06/18/2013 14:59:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[R_AuditMaster](
[ID_Audit] [int] NOT NULL,
[Staking_OverRide] [nchar](10) NULL,
[Staking_StatusFlag] [nchar](10) NULL,
[Staking_LastChanged] [smalldatetime] NULL
) ON [PRIMARY]
GO
-- Create TRigger - copy this table's latest Primary Key (autocounter) and append it to new record in R_AuditMaster Table
USE [RegulatoryDB]
GO
/****** Object: Trigger [R_CopyNewAuditID] Script Date: 06/18/2013 14:55:28 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[R_CopyNewAuditID]'))
DROP TRIGGER [dbo].[R_CopyNewAuditID]
GO
USE [RegulatoryDB] -- Change this for Production DB or Test DB
GO
/****** Object: Trigger [dbo].[R_CopyNewAuditID] Script Date: 06/18/2013 14:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Rx
-- Create date: 6/18/2013
-- Description:New record in Audit - copy new autocounter PK and insert it into table R_CopyNewAuditID
-- =============================================
CREATE TRIGGER [dbo].[R_CopyNewAuditID]
ON [dbo].[Audit]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declaring a Variable (AS keyword is optional in TSQL and doesn't work with Table type)
declare @seq AS int
-- after update of table - grab the new autocounter ID
set @seq = (select max(ID_Audit) from Audit)
-- Insert statements for trigger here
insert into dbo.r_Auditmaster (ID_Audit) values (@seq)
END
GO
June 18, 2013 at 3:50 pm
Another option for your trigger could be
CREATE TRIGGER [dbo].[R_CopyNewAuditID]
ON [dbo].[Audit]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
insert into dbo.r_Auditmaster (ID_Audit) SELECT ID_Audit FROM inserted
END
This will handle instances where there is more than a single insert done to the audit table.
For example, if you do the following
INSERT INTO Audit (SomeValue) SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'your trigger will insert into the audit master for the last row inserted
June 18, 2013 at 4:03 pm
Fantastic point! Thanks.
In this case, the Add New entity is done manually and requires a dozen requirements before the single record can be updated.
However, your example helps with another transaction based task.
Really appreciate the valuable tip.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply