Instead of Trigger

Question

Post reply

Instead of Trigger

Andy Feetenby

Valued Member

Points: 58
More actions
April 18, 2007 at 6:00 am

#195121

I was hoping someone could help me with a problem I am having. I'm pretty new to SQL programming, so apologies if this appears simple.
Using Access forms as my front end, based on a sql view I create a record. I have an Instead of Insert trigger which then sends the form values to the table fields I designate.
I've tried running my Trigger from the Access front end and by running from Query Analyser. Both have the same outcome.
I've declared a variable on my trigger which represents the @@Scope_identity of table C. This I then try to insert into Table B. However when the trigger runs it doesn't either capture/insert the value. So when I query Table B it has a Null value.
The trigger script seems to be the issue, since when I try to run a simple sql script inserting values into a table, then retrieving the @@Identity field of table C it WILL insert the value into Table B.
Hope all that is clear, and I hope you can help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

There is so much that could go wrong. I need to see your code.

Russel Loski, MCSE Business Intelligence, Data Platform

Andy Feetenby Valued Member Points: 58 More actions · Answer 2

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER TRIGGER tr_InsertCR

ON view_InsertCR

Instead Of Insert

As

DECLARE @ReportID int;

DECLARE @SchoolID int;

DECLARE @OfficerID int;

DECLARE @DateOfVisit smallDateTime;

DECLARE @Hours int;

DECLARE @Minutes int;

DECLARE @Activity int;

DECLARE @Funding int;

DECLARE @Reason int;

DECLARE @Method int;

DECLARE @Outcomes varChar(500)

SELECT @ReportID=intReportID From Inserted;

SELECT @SchoolID=intSchoolID From Inserted;

SELECT @OfficerID=intOfficerID From Inserted;

SELECT @DateOfVisit=dtmDateOfVisit From Inserted;

SELECT @Hours=intTimeSpentHours From Inserted;

SELECT @Minutes=intTimeSpentMinutes From Inserted;

SELECT @Activity=intActivity From Inserted;

SELECT @Funding=intFunding From Inserted;

SELECT @Reason=intReason From Inserted;

SELECT @Method=intMethod From Inserted;

SELECT @Outcomes=chrOutcomes From Inserted

INSERT INTO tblCR_Visit(intActivity,intFunding,intReason,intMethod,chrOutcomes)

VALUES(@Activity,@Funding,@Reason,@Method,@Outcomes)

DECLARE @CR_ID Int

SELECT @CR_ID=Ident_Current(intVisitID) FROM tblCR_Visit

INSERT INTO tblRecords(intCR)

VALUES(@CR_ID)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Russel Loski SSCertifiable Points: 7059 More actions · Answer 3

I have not used the Ident_Current function, but according to http://msdn2.microsoft.com/en-us/library/aa933217(SQL.80).aspx you pass it the name of the table:

SET @CR_ID=Ident_Current('tblCR_Visit')

I would be nervous that this might return someone else's identity value (say two people are inserting at precisely the same time.

SET @CR_ID = SCOPE_IDENTITY ()

This returns the last identity value generated in the scope of your query. This would return the identity value generated in your first insert.

Russel Loski, MCSE Business Intelligence, Data Platform