Instead of Trigger

[afeets]

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.

 

[bborissov]

Can you post the code?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[afeets]

As requested

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
-- I've tried @@Scope_Identity As Well

INSERT INTO tblRecords(intCR)
VALUES(@CR_ID)

 

[bborissov]

try:

[COLOR=blue]ALTER[/color] [COLOR=blue]TRIGGER[/color] tr_InsertCR [COLOR=blue]ON[/color] view_InsertCR
Instead [COLOR=blue]Of[/color] [COLOR=blue]Insert[/color]
[COLOR=blue]As[/color]

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] tblCR_Visit(intActivity,intFunding,intReason,intMethod,chrOutcomes)
[COLOR=blue]SELECT[/color] intActivity, intFunding, intReason, intMethod, chrOutcomes
       [COLOR=blue]FROM[/color] Inserted



[COLOR=blue]DECLARE[/color] @CR_ID     [COLOR=blue]Int[/color]
[COLOR=blue]SELECT[/color] @CR_ID=Ident_Current([COLOR=red]'tblCR_Visit'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] tblRecords(intCR)
[COLOR=blue]VALUES[/color](@CR_ID)

But keep in mind that Triggers are fired AFTER whole job is done, that way if you insert many record in view_InsertCR you will add them in tblCR_Visit but in tblRecords you will get only the last ID.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[afeets]

Much appreciated for the help you have given me. The new script works as you suggested. I am however unsure as to why my old one didn't since the only difference I can see is that I declared variables.

 

[bborissov]

Here it is the difference:
Your code:

Ident_Current(intVisitID)

my code:

Ident_Current('tblCR_Visit')

Ident_Current requires table name as parameter, not the field name.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP