Database Trigger Help

[Pho3nix]

Hi all,

Working on a trigger but I can't get the ID row I'm inserting into. Any help would be appreciated.

Below is the code I'm using but it fails

ALTER TRIGGER tr_PCompliance_Insert
ON P1
INSTEAD OF INSERT 
AS
	SET NOCOUNT ON
	
	DECLARE 
	@P1d INT
	SET @P1Id = (SELECT @@Identity)
	INSERT INTO P2 (PId,RuleId,PeriodId,DataId,Data)
	SELECT PD.PId,PD,RuleId,PD.PeriodId,@ProjectDataId,PD.Data
	FROM
		INSERTED PD

ALTER TRIGGER tr_PCompliance_Insert
ON P1
INSTEAD OF INSERT 
AS
	SET NOCOUNT ON
	INSERT INTO P2 (PId,RuleId,PeriodId,DataId,Data)
	SELECT PD.PId,PD,RuleId,PD.PeriodId,PD.Id,PD.Data
	FROM
		INSERTED PD

The one above works but gives me 0. My google-fu is failing me so thought I'd ask here

 

[xrapidx]

ALTER TRIGGER tr_PCompliance_Insert
ON P1
INSTEAD OF INSERT 
AS
	SET NOCOUNT ON
	
	DECLARE 
	@P1d INT
	INSERT INTO P2 (PId,RuleId,PeriodId,DataId,Data)
	SELECT PD.PId,PD,RuleId,PD.PeriodId,@ProjectDataId,PD.Data
	FROM
		INSERTED PD

	SET @P1Id = (SELECT @@Identity)

 

[Pho3nix]

ALTER TRIGGER tr_PCompliance_Insert
ON P1
INSTEAD OF INSERT 
AS
	SET NOCOUNT ON
	
	DECLARE 
	@P1d INT
	INSERT INTO P2 (PId,RuleId,PeriodId,DataId,Data)
	SELECT PD.PId,PD,RuleId,PD.PeriodId,@ProjectDataId,PD.Data
	FROM
		INSERTED PD

	SET @P1Id = (SELECT @@Identity)

Just tried this and I get the error below. I think this is why I moved the SET to the top rather.

Cannot insert the value NULL into column 'DataId', table 'P2'; column does not allow nulls. INSERT fails.

 

[Messugga]

@@IDENTITY returns the last identity value inserted by the statement. What exactly are you trying to achieve?

 

[Pho3nix]

When I insert a new row into the table P1, I want to the ID of the row inserted and use it it in the trigger to set the P2.DataId.
Hope that makes sense.

 

[bin3]

Your ID is not being inserted, it is being generated.

So do the insert, then use @@IDENTITY to get the last inserted ID value, and use that in your update.

probably quite a few other ways of doing it ....

 

[Pho3nix]

It's not being inserted??

	DECLARE 
	@DataId INT
        SET @DataId = (SELECT @@Identity)
	INSERT INTO ProjectDataHistory (ProjectId,ComplianceRuleId,CompliancePeriodId,ProjectDataId,Data)
	SELECT PD.ProjectId,PD.ComplianceRuleId,PD.CompliancePeriodId,@DataId,PD.Data

Sorry if it's a noob question.. My first attempt working triggers.

 

[xrapidx]

You first need to do the insert into P1

Then get the ID

Then do the insert into P2

 

[Pho3nix]

You first need to do the insert into P1

Then get the ID

Then do the insert into P2

Ok, I altered the trigger and use this to insert a new item into the table to trigger it..

INSERT INTO P1 (PId,RuleId,PeriodId,Data)
VALUES (1,24,3315,'Test2');

^This doesn't work because of the trigger and I get the error message I posted below.

Cannot insert the value NULL into column 'DataId', table 'P2'; column does not allow nulls. INSERT fails.

 

[xrapidx]

it needs to look something like this

INSERT INTO P1 (PId,RuleId,PeriodId,Data)
VALUES (INSERTED.Id,INSERTED.RuleId,INSERTED.PeriodId,INSERTED.Data);

DECLARE 	@DataId INT
 SET @DataId = (SELECT @@Identity)

	INSERT INTO P2 (PId,RuleId,PeriodId,DataId,Data)
	SELECT PD.PId,PD,RuleId,PD.PeriodId,@DataId ,PD.Data
	FROM
		INSERTED PD

 

[Pho3nix]

Oh, I see what you mean. Let me explain why I'm doing it the way phrased above.
A SSIS package is what is going to be inserting new rows into table P1 and the trigger has to kick off then and add the rows to P2 aswell.
Hope that makes sense. Lemme go have some food, starvation might be preventing me from thinking straight,

 

[Pho3nix]

Oh, I see what you mean. Let me explain why I'm doing it the way phrased above.
A SSIS package is what is going to be inserting new rows into table P1 and the trigger has to kick off then and add the rows to P2 aswell.
Hope that makes sense. Lemme go have some food, starvation might be preventing me from thinking straight,

 

[Pho3nix]

Figured it out after my meal.
The code below is what I needed to add

SET @DataId = (SELECT IDENT_CURRENT( 'P1' ))

Thanks to everyone who helped

 

[xrapidx]

I'm not 100% sure, but that might not be the safest use of IDENT_CURRENT - it returns the last identity of any session.

So if two process are inserting into the table at once, it could return either one.

 

[Pho3nix]

I'm not 100% sure, but that might not be the safest use of IDENT_CURRENT - it returns the last identity of any session.

So if two process are inserting into the table at once, it could return either one.

SSIS package runs at 3am so doubtful anything else is running around that time.
Let me investigate. Thanks for bringing it up.

 

[diabolus]

I think your real issue is the fact that P1's Insert trigger is fired before the row is actually inserted and thus does not have an identity generated yet.

Have you considered using an AFTER INSERT trigger rather than an INSTEAD of INSERT trigger?

i.e.:

FOR | AFTER

AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

INSTEAD OF

Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers.

I'm not sure if it will work, but i've had this dilemma before with identity fields, and usually i got around it by ensuring whatever trigger i fire is only fired after the insert was committed.

 

[xrapidx]

^^^

Thats why I did the insert in my code.

 

[xrapidx]

SSIS package runs at 3am so doubtful anything else is running around that time.
Let me investigate. Thanks for bringing it up.

Its still a risk... not sure what this is for, but personally, I'd rather do it properly than something starts breaking in production and it takes days to find the cause.