Start Free Trial

asked on

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.

Guy Hengel [angelIII / a3]

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

ASKER

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]

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 ...

ASKER

Thank you…I will test this out, and let you know..

ASKER

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]

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?

ASKER

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

ASKER

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]

please post the code to reproduce on this site and post the link
http://sqlfiddle.com/

ASKER

ASKER

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

ASKER

I posted pieces within the link provided and it worked… Could not send it anywhere.. Not sure if I was supposed to

ASKER

http://sqlfiddle.com/#!9/9eecb7d

Guy Hengel [angelIII / a3]

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 ?

ASKER

Will do

ASKER

InsertedData.xlsx

ASKER

Did you get what you needed

ASKER

Are you still working with me??

Guy Hengel [angelIII / a3]

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.

ASKER

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

ASKER

Sorry.. Yes family does come first..

Guy Hengel [angelIII / a3]

sorry, but that is the update within the trigger. I would like to see the update you run to update SalesOrder table

ASKER

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)

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

This works!!! adding the if statement did it…not sure why..