August 4, 2009 at 7:54 am
We are creating triggers on tables in sql server 2005. When certain data items in certain tables change, we want to record it so we can send the changes to another database located on an ALpha machine. When an insert or delete occurs, we write the relevant identifiers of that table to a sql table called tblTriggers. The update triggers all work beautifully. We were horrified to see that when there is an insert or delete to a table, the correct master-detail table linkage becomes corrupted when viewing in Access 2003 front end form. We see detail records that are not linked to the right master records.
Here's one of the triggers. I'm a newbie at sql so be kind! 🙂 We need to get this done ASAP, and I can not find anyone writing about this problem when I search the internet. Are we doing this wrong? TIA
USE [BART_Test_Copy]
GO
/****** Object: Trigger [dbo].[InsertDeleteCourses] Script Date: 08/04/2009 08:44:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [dbo].[InsertDeleteCourses]
ON [dbo].[EnrollCourses]
AFTER INSERT,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @StudentID nvarchar(20)
Declare @EnrollID int
Declare @CrsOfferID Int
Declare @AcadTerm nvarchar(15)
Declare @TriggerType nvarchar(1)
IF (SELECT COUNT(*) FROM inserted) > 0
Begin
Set @TriggerType = 'I'
Select @StudentID = StudentID, @EnrollID = EnrollID, @CrsOfferID = CrsOfferID From Inserted
End
If (SELECT COUNT(*) FROM deleted) > 0
Begin
Set @TriggerType = 'D'
Select @StudentID = StudentID, @EnrollID = EnrollID, @CrsOfferID = CrsOfferID From deleted
End
Select @AcadTerm = AcadTerm From CourseOfferings Where CrsOfferID = @CrsOfferID
INSERT INTO [dbo].[tblTriggers](TriggerType, TriggerTable, EnrollID,StudentID,CrsOfferID,AcadTerm,TriggerDateTime)
Values(@TriggerType, 'Courses', @EnrollID, @StudentID, @CrsOfferID, @AcadTerm, GetDate())
End
August 4, 2009 at 11:54 am
We figured it out. The identity on a field in tblTriggers was set. This was freaking out the front end Access screens. So, we took the identity value off of the field and it now works.....
August 4, 2009 at 11:58 am
iapearsall, I'm not really sure what you are asking. Do you think you have an Access problem or a SQL Server trigger problem? Without full table definitions and sample data, as well as a detailed description of what your expected results are, it is hard to help. I have taken some liberties with the data, but your trigger seems to be doing just what you are asking it to do. Can you maybe provide more detail, like what is the record source of the Access form? Is it a table, view, stored procedure? What are the PK's of these tables?
/*====================================================================
Set up easily consumable code so we can simply copy and paste into
QA. This code will create some test tables and test data.
===================================================================*/
IF OBJECT_ID('EnrollCourses','u') IS NOT NULL
DROP TABLE EnrollCourses
IF OBJECT_ID('tblTriggers','u') IS NOT NULL
DROP TABLE tblTriggers
CREATE TABLE EnrollCourses
(
StudentID VARCHAR(20),
EnrollID INT,
CrsOfferID INT,
AcadTerm VARCHAR(20)
)
CREATE TABLE tblTriggers
(
TriggerType CHAR(1),
TriggerTable VARCHAR(20),
EnrollID INT,
StudentID VARCHAR(20),
CrsOfferID INT,
AcadTerm VARCHAR(20),
TriggerDateTime DATETIME
)
INSERT INTO EnrollCourses
SELECT 'A1',1,200,'FA09' UNION ALL
SELECT 'A2',2,250,'FA09' UNION ALL
SELECT 'B1',3,300,'FA09' UNION ALL
SELECT 'B2',4,350,'FA09' UNION ALL
SELECT 'C1',5,400,'FA09' UNION ALL
SELECT 'C2',6,450,'FA09'
GO
/****** Object: Trigger [dbo].[InsertDeleteCourses] Script Date: 08/04/2009 08:44:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[InsertDeleteCourses]
ON [dbo].[EnrollCourses]
AFTER INSERT,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @StudentID nvarchar(20)
Declare @EnrollID int
Declare @CrsOfferID Int
Declare @AcadTerm nvarchar(15)
Declare @TriggerType nvarchar(1)
IF (SELECT COUNT(*) FROM inserted) > 0
Begin
Set @TriggerType = 'I'
Select @StudentID = StudentID, @EnrollID = EnrollID, @CrsOfferID = CrsOfferID From Inserted
End
If (SELECT COUNT(*) FROM deleted) > 0
Begin
Set @TriggerType = 'D'
Select @StudentID = StudentID, @EnrollID = EnrollID, @CrsOfferID = CrsOfferID From deleted
End
Select @AcadTerm = 'FA09' --AcadTerm From CourseOfferings Where CrsOfferID = @CrsOfferID
INSERT INTO [dbo].[tblTriggers](TriggerType, TriggerTable, EnrollID,StudentID,CrsOfferID,AcadTerm,TriggerDateTime)
Values(@TriggerType, 'Courses', @EnrollID, @StudentID, @CrsOfferID, @AcadTerm, GetDate())
End
GO
INSERT INTO EnrollCourses
SELECT 'D1',7,500,'FA09'
DELETE EnrollCourses
WHERE StudentID = 'D1'
AND EnrollID = 7
AND CrsOfferID = 500
AND AcadTerm = 'FA09'
--See your results. It seems to be doing what you are asking it to do.
SELECT
*
FROM tblTriggers
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 4, 2009 at 12:03 pm
Identities alone shouldn't cause Access issues.
However, I can see issues with your trigger when you update multiple rows. That would cause issues.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 4, 2009 at 2:18 pm
Yes, I see what you mean.
I've read that you are not supposed to use cursors inside triggers, so how would one write out a record to tblTriggers for each row?
August 4, 2009 at 6:59 pm
as Steve identified, your trigger is designed for just a single row of data.
here's a version that should handle multiple rows gracefully.
compare it to yours and see how it simplified the work.
ALTER TRIGGER [dbo].[InsertDeleteCourses]
ON [dbo].[EnrollCourses]
AFTER INSERT,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
INSERT INTO [dbo].[tblTriggers](TriggerType, TriggerTable, EnrollID,StudentID,CrsOfferID,AcadTerm,TriggerDateTime)
SELECT
'I',
'Courses',
Inserted.EnrollID,
Inserted.StudentID,
Inserted.CrsOfferID,
CourseOfferings.AcadTerm,
GetDate()
From Inserted
INNER JOIN CourseOfferings ON Inserted.CrsOfferID = CourseOfferings.CrsOfferID
END
If (SELECT COUNT(*) FROM deleted) > 0
BEGIN
INSERT INTO [dbo].[tblTriggers](TriggerType, TriggerTable, EnrollID,StudentID,CrsOfferID,AcadTerm,TriggerDateTime)
SELECT
'D',
'Courses',
Deleted.EnrollID,
Deleted.StudentID,
Deleted.CrsOfferID,
CourseOfferings.AcadTerm,
GetDate()
From Deleted
INNER JOIN CourseOfferings ON Deleted.CrsOfferID = CourseOfferings.CrsOfferID
END
END --End Trigger
Lowell
August 5, 2009 at 5:23 am
Thank you for your response to my post. We found someone with the same problem at http://groups.google.com.au/group/microsoft.public.access.adp.sqlserv...
This was the problem (which the person described better than I had done:
first observed the following behaviour in Access ADP forms and tables
but then reproduced in Enterprise Manager. This is variation on a
recent post to microsoft.public.sqlserver -I'm posting here because
I've further refined the problem.
To preempt some advice, the following behaviour wouldn't occur if I
didn't use bound forms in Access ADP. It very much suits my purposes
to use this mode and I'm very reluctant to change that - I prefer to
resolve the basic problem.
I have several tables with insert triggers which in turn insert a
linked record in an another table. This is a necessary client
requirement.
The SQL Server cursor software (please excuse me if my terminology is
unsound) which handles data transfer to and from other applications
(such as not only Access ADP subforms but also Enterprise Manager) uses
@@IDENTITY (rather than SCOPE_IDENTITY() which would do the job
perfectly) to retrieve the ID of a newly inserted record. @@IDENTITY
seems to hold the ID of the last record created anywhere by anyone -
remarkably shoddy coding it seems to me. The upshot of this is that
rather than displaying the newly created record to the user, a record
(coincidentally) with the same ID as the record created by the trigger
routine is displayed.
The solution:
The @@identity is a global value; hence your problem. You must store its
value at the beginning of the trigger and reset it just before the trigger
exits with the following statement from Nick Sestrin:
(http://groups.google.ca/group/microsoft.public.access.adp.sqlserver/b...)
create trigger mytable_insert_trigger on mytable for insert as
declare @identity int
declare @strsql varchar(128)
set @identity=@@identity
--your code
--insert into second table ...
--your code
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1)
as id into #tmp'
execute (@strsql)
August 5, 2009 at 5:24 am
Thank you!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply