January 21, 2015 at 7:54 am
hi
i have a trigger that will not fire due to the data coming from a bulk insert
i have tried to introduce a Cursor to fix this issue, but so far i have failed to get the program to post
the sql code is below, anyone any ideas what i need to do?
mal
GO
/****** Object: Trigger [dbo].[sla_update] Script Date: 01/21/2015 12:00:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter TRIGGER [dbo].[sla_update] ON [dbo].[Order]
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;
declare @salesorder varchar(6)
declare @cust varchar(8)
declare @div varchar(3)
declare @wh varchar(3)
declare @line varchar(8)
declare @orderdate DATETIME
declare @day varchar(4)
declare @time int
declare @cutday varchar(4)
declare @cuttime int
declare @sladate datetime
declare @so varchar(8)
declare @updateflag int
declare @sysprosla datetime
declare @forward_del_day varchar(3)
declare @forwardflag int
declare @custrequest datetime
declare sorder cursor for
select SRREFERENCE
--,@updateflag = SlaUpdate
from inserted
open sla
FETCH NEXT FROM sla INTO @salesorder
WHILE @@FETCH_STATUS = 0
BEGIN
select @sysprosla = cast(AlphaValue as datetime) from SysproCompanyT.dbo.AdmFormData ad where FormType = 'ORD' AND FieldName = 'SLA001'
and ad.KeyField = @salesorder
if @sysprosla is null
begin
select @cust = s.Customer
,@div = ProductGroup,@wh = d.MWarehouse
,@orderdate = OrderDate
,@day = left(datename(dw,s.OrderDate),3)
,@time = left (TrnTime, len (TrnTime)-4) ,
@cutday = Cut_Off_Day,@cuttime = Cut_Off_Time,
@forwardflag = datediff(day,OrderDate,ReqShipDate),
@custrequest = ReqShipDate,
@forward_del_day = Forward_Del_Day
from SysproCompanyT.dbo.SorMaster s
left join
(select a.SalesOrder,TrnTime,a.SalesOrderLine,MStockCode,MWarehouse,
row_number() over(partition by a.SalesOrder Order By TrnTime desc)as rn from SysproCompanyT.dbo.SorAdditions a
join SysproCompanyT.dbo.SorDetail d on d.SalesOrder = a.SalesOrder and d.SalesOrderLine = a.SalesOrderLine and a.SalesOrder = @salesorder)as d
on d.SalesOrder = s.SalesOrder and rn = 1
left join SysproCompanyT.dbo.InvMaster i on i.StockCode = d.MStockCode
join mal.dbo.Delivery_SLAs da on da.Customer = s.Customer and da.Division = ProductGroup and da.Warehouse = d.MWarehouse
where
s.SalesOrder = @salesorder
if @forwardflag <='11'
begin
if @day = 'Mon'and @cutday <> 'Mon'
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays](Monday,@orderdate) as SlaDate from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = 'Tue' and @cutday <> 'Tue'
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays](Tuesday,@orderdate) as SlaDate from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = 'Wed' and @cutday <> 'Wed'
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays](Wednesday,@orderdate) as SlaDate from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = 'Thu' and @cutday <> 'Thu'
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays](Thursday,@orderdate) as SlaDate from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = 'Fri' and @cutday <> 'Fri'
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays](Friday,@orderdate) as SlaDate from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = @cutday and @time <= @cuttime
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays]((case when @day = 'Mon' then Monday when @day = 'Tue' then Tuesday when @day ='Wed' then Wednesday
when @day = 'Thu' then Thursday else Friday end),@orderdate) as SlaDate
from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @day = @cutday and @time > @cuttime
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays]((case when @day = 'Mon' then Tuesday +1 when @day = 'Tue' then Wednesday +1 when @day ='Wed' then Thursday +1
when @day = 'Thu' then Friday +1 else Monday +1 end),@orderdate) as SlaDate
from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
--to deal with mon to fri
if @cutday = 'M2F' and @time > @cuttime
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays]((case when @day = 'Mon' then Tuesday +1 when @day = 'Tue' then Wednesday +1 when @day ='Wed' then Thursday +1
when @day = 'Thu' then Friday +1 else Monday + 1 end),@orderdate ) as SlaDate
from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
else
if @cutday = 'M2F' and @time <= @cuttime
set @sladate = (select SysproCompanyT.dbo.[AddWorkDays]((case when @day = 'Mon' then Monday when @day = 'Tue' then Tuesday when @day ='Wed' then Wednesday
when @day = 'Thu' then Thursday else Friday end),@orderdate) as SlaDate
from mal.dbo.Delivery_SLAs where Customer = @cust and Division = @div and Warehouse = @wh)
update [Order]
set DELIVERYDATE = @sladate
--, SlaUpdate = '1'
where SRREFERENCE = @salesorder
insert into SysproCompanyT.dbo.AdmFormData (FormType,KeyField,FieldName,AlphaValue) values ('ORD',@salesorder,'SLA001',convert(varchar(8),@sladate,112))
end
------------forward orders
-------------
if @sysprosla is null and @forwardflag > '11'
begin
if @forward_del_day = 'Mon'
set @sladate = (select SysproCompanyT.dbo.[ufn_GetFirstDayOfWeek](@custrequest))
else
if @forward_del_day = 'Tue'
set @sladate = (select SysproCompanyT.dbo.[ufn_GetFirstDayOfWeek](@custrequest)+1)
else
if @forward_del_day = 'Wed'
set @sladate = (select SysproCompanyT.dbo.[ufn_GetFirstDayOfWeek](@custrequest)+2)
else
if @forward_del_day = 'Thu'
set @sladate = (select SysproCompanyT.dbo.[ufn_GetFirstDayOfWeek](@custrequest)+3)
else
if @forward_del_day = 'Fri'
set @sladate = (select SysproCompanyT.dbo.[ufn_GetFirstDayOfWeek](@custrequest)+4)
update [Order]
set DELIVERYDATE = @sladate
--, SlaUpdate = '1'
where SRREFERENCE = @salesorder
insert into SysproCompanyT.dbo.AdmFormData (FormType,KeyField,FieldName,AlphaValue) values ('ORD',@salesorder,'SLA001',convert(varchar(8),@sladate,112))
end
if @sysprosla is not null
begin
update [Order]
set DELIVERYDATE = @sysprosla
--, SlaUpdate = '1'
where SRREFERENCE = @salesorder
end
FETCH NEXT FROM sorder INTO
@salesorder
end
close sorder
DEALLOCATE sorder
end
January 21, 2015 at 7:58 am
Bulk Inserts don't fire triggers. Nothing to do with the trigger code (which, from the looks of things needs a lot of work to optimise it). The Bulk Insert itself has to specify the option to fire triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2015 at 8:00 am
ok, thanks Gila
so the program doing the insert needs to be basically told to fire triggers?
yes, first draft, i can work on optimisation once data is posting and is correct - seems i am a little of that yet :@
January 21, 2015 at 9:07 am
got it sorted!
ended up main issue was a security issue of one of the tables i was querying!
mal
January 21, 2015 at 2:18 pm
GilaMonster (1/21/2015)
Bulk Inserts don't fire triggers. Nothing to do with the trigger code (which, from the looks of things needs a lot of work to optimise it). The Bulk Insert itself has to specify the option to fire triggers.
Just hope that this isn't a busy table, either some serious work is needed to re-write the code or some serious work is performed every time the trigger fires.
😎
January 21, 2015 at 2:37 pm
Yea only 100 or so inserts a day, otherwise i wouldnt use a trigger at all
Any tips for making it more efficient? 🙂 always interested in other approaches
January 21, 2015 at 2:45 pm
dopydb (1/21/2015)
Yea only 100 or so inserts a day, otherwise i wouldnt use a trigger at allAny tips for making it more efficient? 🙂 always interested in other approaches
Post the DDL for the table, some sample data in a form of an insert statement with the expected trigger actions/results and I'll have a look
😎
January 21, 2015 at 6:11 pm
dopydb (1/21/2015)
Yea only 100 or so inserts a day, otherwise i wouldnt use a trigger at allAny tips for making it more efficient? 🙂 always interested in other approaches
The answer to your question: replace the CURSOR with a set-based query.
And now a question of my own. What happens when your application becomes the next Facebook and instead of "100 or so" inserts per day it is now doing 100,0000 or so per minute?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 21, 2015 at 11:39 pm
dwain.c (1/21/2015)
dopydb (1/21/2015)
Yea only 100 or so inserts a day, otherwise i wouldnt use a trigger at allAny tips for making it more efficient? 🙂 always interested in other approaches
The answer to your question: replace the CURSOR with a set-based query.
And now a question of my own. What happens when your application becomes the next Facebook and instead of "100 or so" inserts per day it is now doing 100,0000 or so per minute?
Simple. Your face falls off. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2015 at 2:15 am
Guys, you would be shocked at how dbs are loaded with triggers, sps etc etc in the places that i have worked, but dont worry i hear you and know the issues i could hit.
the trigger is a trial run / proof of concept before i get something properly developed within our erp system
if it suddenly gets hit with 1000's of orders then i will have an even better case for the new ERP system that i am looking for 🙂
January 22, 2015 at 2:30 am
Table trigger runs off:
USE [MessageCronusTest]
GO
/****** Object: Table [dbo].[Order] Script Date: 22/01/2015 09:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Order](
[ORDERID] [int] IDENTITY(1,1) NOT NULL,
[ORDERNUMBER] [varchar](20) NOT NULL,
[ORIGINCODE] [varchar](20) NOT NULL,
[CUSTOMERCODE] [varchar](40) NOT NULL,
[ORDERDATE] [datetime] NOT NULL,
[DELIVERYDATE] [datetime] NOT NULL,
[SRCOMMENT] [varchar](255) NULL,
[LINENUMBER] [varchar](10) NOT NULL,
[QUANTITY] [float] NOT NULL,
[PRODUCTCODE] [varchar](20) NOT NULL,
[COMMENT] [varchar](255) NULL,
[ISPICKUP] [char](1) NOT NULL CONSTRAINT [DF_Order_ISPICKUP] DEFAULT ('F'),
[ISPALLATISED] [char](1) NOT NULL CONSTRAINT [DF_Order_ISPALLATISED] DEFAULT ('F'),
[SRREFERENCE] [varchar](20) NULL,
[CREATEDON] [datetime] NOT NULL CONSTRAINT [DF_Order_CREATEDON] DEFAULT (getdate()),
[STATUS] [varchar](20) NOT NULL,
[EXCEPTIONLOG] [varchar](255) NULL,
[REFERENCE] [int] NULL,
[MODIFIEDON] [datetime] NULL,
[ACTIONTYPE] [varchar](1) NULL,
[SlaUpdate] [int] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ORDERID] 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
---------------------------------------------------
-----------------------------------------------------------------------------------------------------------
insert statement
insert into MessageCronusTest values
('111','W05538','GB','HI046','2015-01-21 00:00:00.000','2015-01-21 00:00:00.000','MRAFF TEST:','1','2','10100083','HI046','F','F','W05538','2015-01-21 17:12:49.620','NEW','NULL','NULL','NULL','A','NULL')
-------------------------------------------------
---------------------------------------------------------------------------------
sql for look up table
----------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Delivery_SLAs](
[Customer] [varchar](50) NOT NULL,
[Division] [varchar](2) NOT NULL,
[Warehouse] [varchar](2) NOT NULL,
[Cut_Off_Day] [varchar](3) NULL,
[Cut_Off_Time] [varchar](4) NULL,
[Monday] [int] NOT NULL,
[Tuesday] [int] NOT NULL,
[Wednesday] [int] NOT NULL,
[Thursday] [int] NOT NULL,
[Friday] [int] NOT NULL,
[Forward_Del_Day] [varchar](5) NULL,
CONSTRAINT [PK_Delivery_SLAsLive] PRIMARY KEY CLUSTERED
(
[Customer] ASC,
[Division] ASC,
[Warehouse] 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 ON
GO
--------------------------------------------------------------------
------------------------------------------------------------------------------------
results after trigger
('111','W05538','GB','HI046','2015-01-21 00:00:00.000','2015-01-29 00:00:00.000','MRAFF TEST:','1','2','10100083','HI046','F','F','W05538','2015-01-21 17:12:49.620','NEW','NULL','NULL','NULL','A','NULL')
January 22, 2015 at 2:35 am
insert statement for lookup table
insert into mal.dbo.Delivery_SLAs values
('HI046','GM','GB','Mon','1215','1','5','4','6','2','Tue')
January 23, 2015 at 2:52 pm
declare sorder cursor for
select SRREFERENCE
--,@updateflag = SlaUpdate
from inserted
open sla
FETCH NEXT FROM sla INTO @salesorder
WHILE @@FETCH_STATUS = 0
BEGIN
Would this cursor not only gather one row? (I assume you meant that sorder = sla here). That defeats the purpose I would say.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply