Instead of Trigger

Question

Post reply

Instead of Trigger

Viewing 3 posts - 1 through 4 (of 4 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