Insert Triger on Table A results in Insert on Table B - Newbie

[McChops]

Hi,

SQL Server 2005

I've read through existing posts and used bits & pieces. Think I've got something close.

When an Insert occurs on TABLE_A I want to perform an Insert on TABLE_B.

I want to take TABLE_A.Value1 and insert it into new record TABLE_B.Value1.

I also want to insert the value "Leased" into TABLE_B.Value2.

There is also a where clause which ensures the Trigger only fires for records where TABLE_A.Field3 = 400001

The records are joined on TABLE_A.Value1 and TABLE_B.Value1

This is my trigger;

USE [mdb]
GO
/****** Object: Trigger [dbo].[TR_DF_ci_hardware_workstation] Script Date: 01/12/2009 05:00:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DF_ci_hardware_workstation]
ON [dbo].[TABLE_A] AFTER INSERT
AS

BEGIN

UPDATE dbo.TABLE_B
SET dbo.TABLE_B.Value2 = 'Leased'
WHERE TABLE_A.Value3 = 400001
AND
TABLE_B.Value1 IN
(
SELECT TABLE_B.Value1
FROM Inserted
)

 

[markros]

USE [mdb]
GO
/****** Object: Trigger [dbo].[TR_DF_ci_hardware_workstation] Script Date: 01/12/2009 05:00:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DF_ci_hardware_workstation]
ON [dbo].[TABLE_A] AFTER INSERT
AS

BEGIN

UPDATE B
SET B.Value2 = 'Leased',
B.Value1 = A.Value1 from dbo.Table_B B 
inner JOIN dbo.Table_A A on B.ID = A.ID inner join inserted I on A.ID = I.ID
WHERE A.Value3 = 400001

 

[markros]

See also

http://blogs.lessthandot.com/index....rver/best-practice-coding-sql-server-triggers

 

[McChops]

Hi Markros,

Thanks for your prompt reply. I've played around with your suggestion and suspect I didn't explain myself as well as I could of. I'm pretty sure the following will do the trick;

USE [mdb]
GO
/****** Object:  Trigger [dbo].[TR_DF_ci_hardware_workstation]    Script Date: 12/07/2009 10:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DF_ci_hardware_workstation]
ON [dbo].[ca_owned_resource] AFTER INSERT
AS

BEGIN

INSERT INTO B(B.1,B.2)
SELECT A.1, 'Leased'
FROM Inserted I
WHERE A.3 = 400001
END

However when I perform an activity in the application that executes my trigger, I get an application error mssg in my log that states (to the effect);

SQL Execute  failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3621 SQL State=01000] The statement has been terminated.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=515 SQL State=23000] Cannot insert the value NULL into column 'id', table 'mdb.dbo.TABLE_B'; column does not allow nulls. INSERT fails.

The "id" field of TABLE_B is of course the primary key and required. It's an integer field and incrementally increases by 1 with each insert (via the app). Probably showing my lack of knowledge here, but I assumed the id would look after itself with the Trigger insert. How do I go about getting the insert on table B to occur for the trigger in the same manner as it does via the application?

Hope this makes sense.

Thanks in advance.

 

[markros]

Your insert statement you posted doesn't look correct. Please post the correct insert statement so we can suggest.

 

[McChops]

Previously I replaced table\column names with 'A', 'B', '1', '2', etc to simplify it.

USE [mdb]
GO
/****** Object:  Trigger [dbo].[TR_DF_ci_hardware_workstation]    Script Date: 12/07/2009 10:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DF_ci_hardware_workstation]
ON [dbo].[ca_owned_resource] AFTER INSERT
AS

BEGIN

INSERT INTO dbo.ci_hardware_workstation(ext_asset,leased_or_owned_status)
SELECT own_resource_uuid, 'Leased'
FROM Inserted I
WHERE resource_family = 400001
END

 

[markros]

Did you verify that ci_Hardware_WorkStation has identity field?

May be we need to explicitly add

SET IDENTITY_INSERT dbo.ci_hardware_workstation OFF
before insert command into the trigger?

 

[McChops]

Does it matter where I place it? Did the following;

USE [mdb]
GO
/****** Object:  Trigger [dbo].[TR_DF_ci_hardware_workstation]    Script Date: 12/07/2009 10:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET IDENTITY_INSERT dbo.ci_hardware_workstation OFF
GO
ALTER TRIGGER [dbo].[TR_DF_ci_hardware_workstation]
ON [dbo].[ca_owned_resource] AFTER INSERT
AS

BEGIN

INSERT INTO.....

Get the following error when trying to update the trigger;

Msg 8106, Level 16, State 1, Line 1
Table 'dbo.ci_hardware_workstation' does not have the identity property. Cannot perform SET operation.

 

[markros]

Well, the message is self-explanatory. As I suspected, your table doesn't have identity column, which explains the error you got earlier.

You need to either fix your table by adding identity column (it is usually harder to do on the table with data) or always supply ID field, which is tricky...

 

[McChops]

Thanks for all of your assistance Markros. I'll search for threads on ID field generating and see if I can make use of.