SQL Insert Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Insert Trigger

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-08-17 : 11:20:27

Below is a SQL trigger that lets me append first 5 characters of vendor field to the PO number for an order for distinction.

USE [Apollo_Test]
GO
/****** Object: Trigger [dbo].[BLGH_TR_InvOrders] Script Date: 08/17/2009 10:13:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BLGH_TR_InvOrders]
ON [dbo].[InvOrders]
AFTER INSERT, UPDATE
AS
Update InvOrders
Set InvOrders.PO_Number = InvOrders.PO_Number + substring(InvOrders.vendor, 1, 5)
From InvOrders, Inserted
Where InvOrders.PO_Number = Inserted.PO_Number

It works fine in that it appends the first 5 characters BUT when user goes into edit mode of an order and makes an update, it appends the 5 characters AGAIN. I have changed my trigger to exclude the UPDATE keyword so that it will only do it on an insert -

USE [Apollo_Test]
GO
/****** Object: Trigger [dbo].[BLGH_TR_InvOrders] Script Date: 08/17/2009 10:13:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BLGH_TR_InvOrders]
ON [dbo].[InvOrders]
AFTER INSERT
AS
Update InvOrders
Set InvOrders.PO_Number = InvOrders.PO_Number + substring(InvOrders.vendor, 1, 5)
From InvOrders, Inserted
Where InvOrders.PO_Number = Inserted.PO_Number

With this change, my trigger no longer append the first 5 chars on an update but it also no longer works on an insert. How should code my trigger so it only works on an insert? Any help would be greatly appreciated. Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 12:56:37

>>but it also no longer works on an insert

I don't see any reason this would be the case. What specifically do you mean by "no longer works"?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 13:01:05

It works for me:


use tempdb
go
create table InvOrders (PO_Number varchar(20), vendor varchar(10))
go
create TRIGGER [dbo].[BLGH_TR_InvOrders]
ON [dbo].[InvOrders]
AFTER INSERT
AS
Update InvOrders
Set InvOrders.PO_Number = InvOrders.PO_Number + substring(InvOrders.vendor, 1, 5)
From InvOrders, Inserted
Where InvOrders.PO_Number = Inserted.PO_Number
go

insert InvOrders (PO_Number, vendor)
values ('99999', '00001')

select * from InvOrders
go
drop table InvOrders

OUTPUT:
PO_Number            vendor
-------------------- ----------
9999900001           00001

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources