SQL Triggers

I have the following trigger

Create TRIGGER [dbo].[SorMasterInfoDates] ON [dbo].[SorMaster]
AFTER INSERT
AS

INSERT INTO SCustom.dbo.SalesOrderInfo
        (SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator,  OrdEnd)

SELECT SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator, GetDate() as OrdEnd
from inserted
Where OrderStatus = '1' and cast(GetDate() as date)  = cast(EntrySystemDate as date)
 


This works fine for inserting records into the SalesOrderInfo table within the SCustom database… The problem is if the SalesOrder from SorMaster is edited after it is 'inserted' I need the OrdEnd (a date time field) to reflect that date change…

I am not able to figure this out…Can someone please help me out with what I believe is an Update Trigger??  I am so confused and have spent many many hours trying to add an Update Trigger that only adds many more lines to the SCustom.dbo.SalesOrderInfo table which is what I don't want to happen.
Michael KatzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to UPDATE in a dedicated update trigger?
Create TRIGGER [dbo].[SorMasterInfoDatesUpdate] ON [dbo].[SorMaster]
AFTER UPDATE
AS

UPDATE soi
   SET soi.OrdEnd = getdate()
FROM SCustom.dbo.SalesOrderInfo soi
JOIN  inserted i
  on i.SalesOrder = soi.SalesOrder

Open in new window

Michael KatzAuthor Commented:
I guess I have a stupid question…..But should I have a dedicated update trigger or does combining (like I have read in many articles on sql) with the Insert trigger make more sense??

Not really an expert
Guy Hengel [angelIII / a3]Billing EngineerCommented:
while you can write triggers to handle both insert and updates, in the end I tend to keep the 2 separate unless really the same thing needs to happen ...
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Michael KatzAuthor Commented:
Thank you…I will test this out, and let you know..
Michael KatzAuthor Commented:
It looks like this is changing all the dates for all the orders…I only want it to change on the single order that is being used
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would only be the case if the update/join in the trigger would not be limiting enough (I presumed that SalesOrder is the primary key field?) , or if the update on the table (that raises the trigger) is indeed updating all the rows from the table?
Michael KatzAuthor Commented:
So what I need to do.. At least reading between the lines is recreate my table…that won't allow dupes…with the Key of SalesOrder… Reapply the update trigger…

Make sense??

Like I said I am no guru
Michael KatzAuthor Commented:
So it is not working…I have 30 rows all with unique SalesOrder #'s…the only field I want to update when the trigger runs for ONE of those SalesOrders is the OrdEnd field .. I need it to be the get date() when after update trigger runs..

It is currently updating all records for the OrdEnd field in every one of the 30 rows…
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please post the code to reproduce on this site and post the link
http://sqlfiddle.com/
Michael KatzAuthor Commented:
Michael KatzAuthor Commented:
Created the Table..
CREATE TABLE [dbo].[SalesOrderExtra](
	[SalesOrder] [varchar](20) NOT NULL,
	[OrderStatus] [varchar](1) NOT NULL,
	[OrderDate] [datetime] NULL,
	[EntrySystemDate] [datetime] NULL,
	[ReqShipDate] [datetime] NULL,
	[OrdAcknwPrinted] [char](1) NOT NULL,
	[OrderStatusFail] [char](1) NOT NULL,
	[LastOperator] [varchar](20) NOT NULL,
	[Operator] [varchar](20) NOT NULL,
	[OrdEnd] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[SalesOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

----------------------------------------------------

Open in new window


Then using this to Insert Data as the Original Transactions come in

ALTER TRIGGER [dbo].[SorMasterInfoDates] ON [dbo].[SorMaster]
AFTER Insert
AS

INSERT INTO SalesOrderExtra
        (SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator,  OrdEnd)

SELECT SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator, GetDate() as OrdEnd
from inserted 

Where  cast(GetDate() as date)  = cast(EntrySystemDate as date)



----------------------------------------------------

Open in new window

Script will then update the OrdEnd field with this script

ALTER TRIGGER [dbo].[SalesOrderExtraUpdate] ON [SorMaster]
AFTER UPDATE
AS

UPDATE soi
   SET soi.OrdEnd = getdate()
FROM SalesOrderExtra soi
inner JOIN  inserted i
  on i.SalesOrder = soi.SalesOrder

Open in new window

After I have edited within the Front End program a SalesOrder…the SalesOrderExtra.OrdEnd field changes to the GetDate() but for all orders in SalesOrderExtra..rather just the one specific SalesOrder just edited
Michael KatzAuthor Commented:
I posted pieces within the link provided and it worked… Could not send it anywhere.. Not sure if I was supposed to
Michael KatzAuthor Commented:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so far the setup. can you please now add up some "insert" statement(s) to actually fill the table, and then the update statement you run ?
Michael KatzAuthor Commented:
Will do
Michael KatzAuthor Commented:
Michael KatzAuthor Commented:
Did you get what you needed
Michael KatzAuthor Commented:
Are you still working with me??
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, just side-tracked by job and family.
so far, I have all, except the actual UPDATE statement that you are using to update the salesorder.
Michael KatzAuthor Commented:
ALTER TRIGGER [dbo].[SalesOrderExtraUpdate] ON [dbo].[SorMaster]
AFTER UPDATE
AS

UPDATE soi
   SET soi.OrdEnd = getdate()
FROM SalesOrderExtra soi
inner JOIN  inserted i
  on i.SalesOrder = soi.SalesOrder


I believe its the same one you sent me originally
Michael KatzAuthor Commented:
Sorry.. Yes family does come first..
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, but that is the update within the trigger. I would like to see the update you run to update SalesOrder table
Michael KatzAuthor Commented:
Other than this code (below) the SorMaster table is from an ERP system..Which we cannot do anything with (per se)

So the below code is what initially populates the SalesOrderExtra ….This code and the one you sent previously are the only 2


ALTER TRIGGER [dbo].[SorMasterInfoDates] ON [dbo].[SorMaster]
AFTER Insert
AS

INSERT INTO SalesOrderExtra
        (SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator,  OrdEnd)

SELECT SalesOrder, OrderStatus, OrderDate,EntrySystemDate,ReqShipDate,  OrdAcknwPrinted, OrderStatusFail,
LastOperator, Operator, GetDate() as OrdEnd
from inserted

Where  cast(GetDate() as date)  = cast(EntrySystemDate as date)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue must then come from the ERP which is updating all the rows of  [dbo].[SorMaster], which makes that your trigger ([dbo].[SalesOrderExtraUpdate] ) is updating indeed all the rows.
so, unless you have some criteria to distinguish between some "real user" update in that table and maybe a system wide update of the table,  no way to get that solved.

for example, you could "limit" the update trigger to only update the Extra table if a single row is updated in SorMaster:
ALTER TRIGGER [dbo].[SalesOrderExtraUpdate] ON [dbo].[SorMaster]
AFTER UPDATE
AS
IF @@ROWCOUNT = 1

UPDATE soi
   SET soi.OrdEnd = getdate()
FROM SalesOrderExtra soi
inner JOIN  inserted i
  on i.SalesOrder = soi.SalesOrder

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael KatzAuthor Commented:
This works!!! adding the if statement did it…not sure why..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.