Deadlock :-( - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Deadlock :-(

Author

Topic

ngirishan
Starting Member

5 Posts

Posted - 2010-02-03 : 05:33:28

Hi,

I am using a sql query (having Variable declarations, multiple IF loop, if exist, update and set statements) inside my trigger to update a field based on the result.

this field will also be updated by my application.

I am not able to find out how and when the deadlock is occuring.

is there any way to find out the condition in which i can find out what is causing the issue?

Need this badly as my AS* is depended on this

Thanks in Advance

Nithin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-03 : 05:45:17

quote:
(having Variable declarations, multiple IF loop, if exist, update and set statements) inside my trigger

work of the devil

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 06:06:54

"work of the devil"

I second that!

Post the code of your Trigger, folk here will give you advice.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-03 : 06:24:09

Enable DBCC TRACEON(1204,-1,3604) -- SQL 2000
DBCC TRACEON(1222,-1) --- SQL 2005

and post the result so we can help you.

Read this for how to understand:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138686

ngirishan
Starting Member

5 Posts

Posted - 2010-02-03 : 06:28:55

"work of the devil" - guess so..

below id the code
================================

IF EXISTS (SELECT * FROM sysobjects WHERE NAME='tr_mg_sap_update_export_status_insert' AND xtype='TR')
DROP TRIGGER tr_mg_sap_update_export_status_insert
GO

CREATE TRIGGER tr_mg_sap_update_export_status_insert ON fb_field_value
AFTER INSERT

AS

DECLARE
@field_id INT,
@object_id INT,
@object_id_str VARCHAR(40),
@module_id INT,
@object_type_id INT,
@status VARCHAR(25),
@fob_type VARCHAR(10),
@release VARCHAR(5),
@season_id INT,
@node_id INT,
@fabric_status INT,
@sample_request_id INT

SELECT @object_id = ISNULL(object_id,0), @object_type_id = ISNULL(object_type_id,0), @object_id_str = ISNULL(object_id_str,0), @field_id = ISNULL(field_id,0) FROM INSERTED WHERE field_id IN (100533, 100534, 100535, 100560, 100561, 100562)

IF @field_id IN (100533, 100534, 100535, 100560, 100561, 100562)
BEGIN

-- Style Master

IF @object_id <> 0 AND @object_type_id = 401 AND @field_id = 100535

BEGIN

DECLARE @product_hierarchy VARCHAR(18), @price VARCHAR(10)

SET @node_id = (SELECT TOP 1 ISNULL(ppsy.node_id,0) FROM pm_product_season_year ppsy where
status_id IN (2,8,41482,41483) AND ppsy.[status] = 1 AND ppsy.product_id = @object_id)

SET @season_id = (SELECT TOP 1 fn.season_id FROM fb_node fn WHERE fn.node_id = @node_id)

SET @price = (SELECT
CASE
WHEN LEN(LEFT(cast(K.target_retail_price as VARCHAR(10)),charindex('.', cast(K.target_retail_price as VARCHAR(10)))-1)) = 3
THEN '0' + LEFT(cast(K.target_retail_price as VARCHAR(10)),charindex('.', cast(K.target_retail_price as VARCHAR(10)))-1)
WHEN LEN(LEFT(cast(K.target_retail_price as VARCHAR(10)),charindex('.', cast(K.target_retail_price as VARCHAR(10)))-1)) = 4
THEN LEFT(cast(K.target_retail_price as VARCHAR(10)),charindex('.', cast(K.target_retail_price as VARCHAR(10)))-1)
END
FROM pm_product_season_year C
INNER JOIN pm_carryover_size_combination K ON K.season_year_id = C.season_year_id WHERE C.product_id = @object_id)

SET @product_hierarchy = (SELECT DISTINCT TOP 1 LEFT(MDRM.plm_brand_code +
SUBSTRING (dbo.fbf_get_category_item_code(B.class_id),1,2) + ' ' +
SUBSTRING (dbo.fbf_get_category_item_code(U1.value_id),1,4) +
SUBSTRING (dbo.mg_sap_product_core_noncore(@object_id),1,4) ,18)
FROM pm_product_detail b
INNER JOIN pm_product_season_year C ON B.product_id = C.product_id
INNER JOIN pm_product_brand I ON B.product_id = I.product_id
INNER JOIN fb_field_value U1 ON B.product_id = U1.object_id_str AND U1.field_id = 100559 AND U1.object_type_id = 401 AND U1.module_id = 4 -- Level 3
INNER JOIN mg_division_retek_mapping MDRM ON UPPER(MDRM.plm_brand_desc) = UPPER(dbo.fbf_get_category_item_value(I.brand_id))
WHERE B.product_id = @object_id
)

SET @product_hierarchy = @product_hierarchy + isnull(@price,'')

SET @status = (
SELECT CASE WHEN COUNT(A.product_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
FROM pm_product A
INNER JOIN pm_product_detail b ON A.product_id = B.product_id
INNER JOIN pm_product_season_year C ON A.product_id = C.product_id
INNER JOIN fb_node D ON C.node_id = D.node_id
INNER JOIN pm_bom E ON A.product_id = E.product_id
INNER JOIN pm_bom_fabric F ON F.bom_id = E.bom_id
INNER JOIN ft_fabric G ON E.object_id = G.fabric_id
INNER JOIN ft_fabric_detail H ON G.fabric_id = H.fabric_id
INNER JOIN pm_product_brand I ON A.product_id = I.product_id
INNER JOIN pm_product_size_combination J ON A.product_id = J.product_id
INNER JOIN pm_carryover_size_combination K ON K.season_year_id = C.season_year_id
INNER JOIN pm_product_colorway L ON A.product_id = L.product_id
INNER JOIN mg_stagging_product_hierarchy M ON M.level5 = @product_hierarchy
INNER JOIN mg_sap_master_data_material_group N ON UPPER(N.material_description) = UPPER(dbo.fbf_get_category_item_value(B.class_id))
INNER JOIN mg_division_retek_mapping MDRM ON UPPER(MDRM.plm_brand_desc ) = UPPER(dbo.fbf_get_category_item_value(I.brand_id))

INNER JOIN fb_field_value U1 ON A.product_id = U1.object_id_str AND U1.field_id = 100087 AND U1.object_type_id = 401 AND U1.module_id = 4 -- basic material
INNER JOIN fb_field_value U2 ON A.product_id = U2.object_id_str AND U2.field_id = 100088 AND U2.object_type_id = 401 AND U2.module_id = 4 -- gross weight
INNER JOIN fb_field_value U3 ON A.product_id = U3.object_id_str AND U3.field_id = 100089 AND U3.object_type_id = 401 AND U3.module_id = 4 -- net weight
INNER JOIN fb_field_value U4 ON A.product_id = U4.object_id_str AND U4.field_id = 100090 AND U4.object_type_id = 401 AND U4.module_id = 4 -- domesti imported
LEFT JOIN fb_field_value U5 ON A.product_id = U5.object_id_str AND U5.field_id = 100091 AND U5.object_type_id = 401 AND U5.module_id = 4 -- ind std
INNER JOIN fb_field_value U6 ON A.product_id = U6.object_id_str AND U6.field_id = 100092 AND U6.object_type_id = 401 AND U6.module_id = 4 -- price validity
INNER JOIN fb_field_value U7 ON A.product_id = U7.object_id_str AND U7.field_id = 100043 AND U7.object_type_id = 401 AND U7.module_id = 4 -- plant
INNER JOIN fb_field_value U8 ON A.product_id = U8.object_id_str AND U8.field_id = 100402 AND U8.object_type_id = 401 AND U8.module_id = 4 -- product variation
INNER JOIN fb_field_value U9 ON A.product_id = U9.object_id_str AND U9.field_id = 100174 AND U9.object_type_id = 401 AND U9.module_id = 4 -- mode of sourcing
INNER JOIN fb_field_value U10 ON A.product_id = U10.object_id_str AND U10.field_id = 100010 AND U10.object_type_id = 401 AND U10.module_id = 4 -- FG-SWATCHSET --Month Drop
WHERE A.current_version = 1
AND F.main_fabric = 1
AND B.product_no != ''
AND B.design_no != ''
AND dbo.fbf_get_category_item_code(H.color_pattern_id)!= ''
AND B.short_description != ''
AND E.quantity > 0
AND J.size_range_id != 0
AND K.target_retail_price > 0
AND L.status_id NOT IN (3002, 41584)
AND dbo.fbf_get_category_item_code(J.size_range_id) != ''
AND ISNULL(MDRM.plm_brand_desc,'') != ''
AND ISNULL(J.identification,'') != ''
AND (ISNULL(dbo.fbf_get_category_item_code(U4.value_id),'')) != ''
AND (ISNULL(dbo.fbf_get_category_item_code(U7.value_id),'')) != ''
AND (ISNULL(dbo.fbf_get_category_item_code(U8.value_id),'')) != ''
AND (ISNULL(dbo.fbf_get_category_item_code(U9.value_id),'')) != ''
AND (ISNULL(dbo.fbf_get_category_item_code(U10.value_id),'')) != ''
AND U1.value_text != ''
AND U2.value_text != ''
AND U3.value_text != ''
AND U6.value_text != ''

AND A.product_id = @object_id
AND ISNULL((SELECT TOP 1 blend_code FROM mg_stagging_fabric_blend
WHERE blend_desc = (dbo.mg_sap_fabric_material_content(G.fabric_id))),'') != ''
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = CAST(@object_id AS VARCHAR(5)) AND field_id = 100535

IF @status = 'Ready for Export'
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_stagging_products_for_sap') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
IF @object_id > 0 AND NOT EXISTS (SELECT product_id FROM mg_stagging_products_for_sap
WHERE product_id = @object_id and flag = 1)
AND @season_id IN (45460, 45478, 45479, 45502, 45503)
BEGIN
INSERT INTO mg_stagging_products_for_sap(product_id, product_hierarchy)
VALUES(@object_id, @product_hierarchy)
END

IF @object_id > 0 AND EXISTS (SELECT product_id FROM mg_stagging_products_for_sap
WHERE product_id = @object_id and flag = 1)
AND @season_id IN (45460, 45478, 45479, 45502, 45503)
BEGIN
UPDATE mg_stagging_products_for_sap SET export_date = GETDATE()
WHERE product_id = @object_id and flag = 1
END
END
END
END

-- Fabric Master

IF @object_id <> 0 AND @object_type_id = 3305 AND @field_id = 100533

BEGIN
SET @node_id = (SELECT TOP 1 ISNULL(ftsy.ft_fabricnodeid,0) FROM ft_fabric_season_year ftsy WHERE ftsy.status_id = 41488
AND ftsy.status = 1 AND ftsy.fabric_id = @object_id)

SET @season_id = (SELECT TOP 1 fn.season_id FROM fb_node fn WHERE fn.node_id = @node_id)

SET @status = (
SELECT CASE WHEN COUNT(FT.fabric_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
FROM ft_fabric_season_year FTSY
INNER JOIN ft_fabric FT ON FTSY.fabric_id = FT.fabric_id
INNER JOIN ft_fabric_detail FTD ON FTSY.fabric_id = FTD.fabric_id
INNER JOIN ft_fabric_brand FTB ON FTSY.fabric_id = FTB.fabric_id
INNER JOIN ft_fabric_colorway_detail FTCD ON FTSY.fabric_id = FTCD.fabric_id
INNER JOIN ft_fabric_ownership FTO ON FTSY.fabric_id = FTO.fabric_id AND FTO.mill_location_id != 0
INNER JOIN mg_sap_master_data_material_group MG ON UPPER(MG.material_description)
= UPPER(dbo.fbf_get_category_item_value(ftd.fabric_type_id))
INNER JOIN fb_field_value U1 ON FTSY.fabric_id = U1.object_id_str
AND U1.field_id = 100041 AND U1.object_type_id = 3305
AND U1.module_id = 3 -- deskloomreq
WHERE FT.current_version = 1
AND FTD.description != ''
AND MG.material_description != ''
AND dbo.mg_sap_fabric_get_ownership(FTSY.fabric_id) != ''
AND dbo.mg_sap_fabric_core_noncore(FTSY.fabric_id) != ''
AND (isnull(dbo.fbf_get_category_item_code(U1.value_id),'')) != ''
AND FTD.end_use_id > 0
AND FTSY.status = 1
AND FTCD.status_id NOT IN (3802, 41583)
AND FTSY.fabric_id = @object_id
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = CAST(@object_id AS VARCHAR(5)) AND field_id = 100533

IF @status = 'Ready for Export'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_stagging_fabrics_for_sap') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
BEGIN
IF @object_id > 0 AND NOT EXISTS (SELECT fabric_id FROM mg_stagging_fabrics_for_sap
WHERE fabric_id = @object_id and flag = 1)
AND @season_id IN (45460, 45478, 45479, 45502, 45503)
BEGIN
INSERT INTO mg_stagging_fabrics_for_sap(fabric_id)
VALUES(@object_id)
END

IF @object_id > 0 AND EXISTS (SELECT fabric_id FROM mg_stagging_fabrics_for_sap
WHERE fabric_id = @object_id and flag = 1)
AND @season_id IN (45460, 45478, 45479, 45502, 45503)
BEGIN
UPDATE mg_stagging_fabrics_for_sap SET export_date = GETDATE()
WHERE fabric_id = @object_id and flag = 1
END
END
END
END

-- Trim Master

IF @object_id <> 0 AND @object_type_id = 3403 AND @field_id = 100534

BEGIN
SET @node_id = (SELECT TOP 1 ISNULL(ftsy.ft_trimnodeid,0) FROM ft_trim_season_year ftsy WHERE ftsy.status_id = 11701
AND ftsy.status = 1 AND ftsy.trim_id = @object_id)

SET @season_id = (SELECT TOP 1 fn.season_id FROM fb_node fn WHERE fn.node_id = @node_id)

SET @status = (
SELECT CASE WHEN COUNT(FT.trim_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
FROM ft_trim_season_year FTSY
INNER JOIN ft_trim FT ON FTSY.trim_id = FT.trim_id
INNER JOIN ft_trim_detail FTD ON FTSY.trim_id = FTD.trim_id
INNER JOIN FT_trim_brand FTB ON FTSY.trim_id = FTB.trim_id
INNER JOIN ft_trim_ownership FTO ON FTSY.trim_id = FTO.trim_id
INNER JOIN ft_trim_colorway_detail FTCD ON FTSY.trim_id = FTCD.trim_id AND FTCD.colorway_no != ''
INNER JOIN ft_trim_costing FTC ON FTSY.trim_id = FTC.trim_id AND FTSY.trim_season_year_id = FTC.trim_season_year_id
INNER JOIN mg_sap_master_data_material_group MSMDMG ON UPPER(MSMDMG.material_description) = UPPER(dbo.fbf_get_category_item_value(ftd.trim_type_id))
INNER JOIN fb_field_value U1 ON FTSY.trim_id = U1.object_id_str AND U1.field_id = 100072 AND U1.object_type_id = 3403 AND U1.module_id = 3 --plant
INNER JOIN fb_field_value U2 ON FTSY.trim_id = U2.object_id_str AND U2.field_id = 100073 AND U2.object_type_id = 3403 AND U2.module_id = 3 --storage location
WHERE FT.current_version = 1
AND FTSY.[status] = 1
AND FTSY.trim_id = @object_id
AND FTD.description != ''
AND (isnull(dbo.fbf_get_category_item_code(FTC.fob_price_uom_id),'')) != ''
AND (isnull(dbo.fbf_get_category_item_code(U1.value_id),'')) != ''
AND (isnull(dbo.fbf_get_category_item_code(U2.value_id),'')) != ''
AND FTO.supplier_location_id > 0
AND FTCD.status_id NOT IN (3802, 41583)
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = CAST(@object_id AS VARCHAR(5)) AND field_id = 100534

IF @status = 'Ready for Export'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_stagging_trims_for_sap') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
BEGIN
IF @object_id > 0 AND NOT EXISTS (SELECT trim_id FROM mg_stagging_trims_for_sap
WHERE trim_id = @object_id and flag = 1) AND @season_id IN (45490, 45481)
BEGIN
INSERT INTO mg_stagging_trims_for_sap(trim_id)
VALUES(@object_id)
END

IF @object_id > 0 AND EXISTS (SELECT trim_id FROM mg_stagging_trims_for_sap WHERE trim_id = @object_id and flag = 1)
AND @season_id IN (45490, 45481)
BEGIN
UPDATE mg_stagging_trims_for_sap SET export_date = GETDATE() WHERE trim_id = @object_id and flag = 1
END
END
END
END

SET @fob_type = (SELECT TOP 1 value_text FROM fb_field_value WHERE field_id = 100420 AND module_id = 4 AND object_id_str = @object_id_str)
SET @release = (SELECT TOP 1 value_text FROM fb_field_value WHERE field_id = 100086 AND module_id = 4 AND object_id_str = @object_id_str)

-- SMS PO Status For SAP

IF @object_id_str !='' AND @field_id = 100560 and @fob_type = 'FOB'

BEGIN
SET @status = (
SELECT CASE WHEN COUNT(A.product_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
FROM pm_product A
INNER JOIN pm_product_detail B ON A.product_id = B.product_id
INNER JOIN pm_product_season_year C ON A.product_id = C.product_id
INNER JOIN fb_node D ON C.node_id = D.node_id
INNER JOIN pm_sample_request E ON A.product_id = E.product_id
INNER JOIN pm_sample F ON E.request_id = F.request_id
LEFT JOIN pm_sr_requested_to G ON E.request_id = G.request_id AND G.item_flag = 1
LEFT JOIN CPM_Company H ON G.location_id = H.CPM_CompanyID
LEFT JOIN CPM_Company J ON H.CPM_ParentID = J.CPM_CompanyID
LEFT JOIN CPM_CompanyLocationInfo K ON G.location_id = K.CPM_CompanyID
LEFT JOIN cpm_companyinfo cc1 ON cc1.CPM_CompanyID = J.CPM_CompanyID

INNER JOIN fb_field_value U1 ON CAST(A.product_id AS VARCHAR) + ',' + CAST(E.request_id AS VARCHAR) = U1.object_id_str AND U1.field_id = 100421 AND U1.module_id = 4 -- plant
INNER JOIN fb_field_value U2 ON CAST(A.product_id AS VARCHAR) + ',' + CAST(E.request_id AS VARCHAR) = U2.object_id_str AND U2.field_id = 100422 AND U2.module_id = 4 -- storage Location
INNER JOIN fb_field_value U3 ON CAST(A.product_id AS VARCHAR) + ',' + CAST(E.request_id AS VARCHAR) = U3.object_id_str AND U3.field_id = 100423 AND U3.module_id = 4 -- Purcahse group
INNER JOIN fb_field_value U4 ON CAST(A.product_id AS VARCHAR) + ',' + CAST(E.request_id AS VARCHAR) = U4.object_id_str AND U4.field_id = 100084 and U4.module_id = 4 -- Price
INNER JOIN fb_field_value U5 ON A.product_id = U5.object_id AND U5.field_id = 100557 AND U5.object_type_id = 401 AND U5.module_id = 4 -- Tax Code

WHERE

A.current_version = 1
AND C.status = 1
AND F.colorway_no != ''
AND CC1.CPM_CompInfoClientCode !=''
AND (isnull(dbo.fbf_get_category_item_code(F.size_id),'')) != ''
AND F.quantity > 0
and E.need_date != ''
AND E.request_date != ''
AND (isnull(dbo.fbf_get_category_item_code(F.quantity_uom_id),'')) != ''
AND U4.value_text !=''
AND E.request_type_id = 44795
and (isnull(dbo.fbf_get_category_item_code(U1.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U2.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U3.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U5.value_id),'')) != ''
AND CAST(A.product_id AS VARCHAR) + ',' + CAST(E.request_id AS VARCHAR) = @object_id_str
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = @object_id_str AND field_id = 100560

SET @sample_request_id = (SELECT CAST(SUBSTRING (@object_id_str,CHARINDEX(',', @object_id_str) + 1 ,LEN(@object_id_str)) AS INT))

IF @status = 'Ready for Export' AND @sample_request_id > 0 AND @release = 'Yes'
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_sap_request_for_sap_sms') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
IF NOT EXISTS (SELECT sample_request_id FROM mg_sap_request_for_sap_sms
WHERE sample_request_id = @sample_request_id and flag = 1 AND sms_type = 1)
BEGIN
INSERT INTO mg_sap_request_for_sap_sms
(
product_id,
sample_request_id,
sample_id,
sms_type,
flag
)
SELECT DISTINCT psr.product_id, psr.request_id, ps.sample_id, 1, 1
FROM pm_sample_request psr
INNER JOIN pm_sample ps ON ps.request_id = psr.request_id
WHERE psr.request_id = @sample_request_id
END
END
END
END

-- SMS WO Status For SAP
IF @object_id_str !='' AND @field_id = 100560 and @fob_type = 'Non FOB'
BEGIN
SET @status = (
SELECT CASE WHEN COUNT(pp.product_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
FROM pm_product pp
INNER JOIN pm_product_detail ppd ON ppd.product_id = pp.product_id
INNER JOIN pm_product_season_year ppsy ON ppsy.product_id = pp.product_id
INNER JOIN pm_sample_request psr ON psr.product_id = pp.product_id
INNER JOIN pm_sample ps ON ps.request_id = psr.request_id
INNER JOIN fb_field_value ffv1 ON CAST(pp.product_id AS VARCHAR) + ',' + CAST(ps.request_id AS VARCHAR) = ffv1.object_id_str and ffv1.field_id = 100421 and ffv1.module_id = 4 -- Production Plant
INNER JOIN fb_field_value ffv3 ON CAST(pp.product_id AS VARCHAR) = ffv3.object_id_str and ffv3.field_id = 100402 and ffv3.module_id = 4 -- Product variation
WHERE
psr.request_type_id = 44795
AND pp.current_version = 1
AND ppsy.status = 1
AND ps.colorway_no != ''
AND dbo.fbf_get_category_item_code(ffv1.value_id) != ''
AND dbo.fbf_get_category_item_code(ffv3.value_id) != ''
AND dbo.fbf_get_category_item_code(ps.size_id) != ''
AND psr.request_date != ''
AND psr.need_date != ''
AND psr.need_date != ''
AND CAST(pp.product_id AS VARCHAR) + ',' + CAST(ps.request_id AS VARCHAR) = @object_id_str
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = @object_id_str AND field_id = 100560

SET @sample_request_id = (SELECT CAST(SUBSTRING (@object_id_str,CHARINDEX(',', @object_id_str) + 1 ,LEN(@object_id_str)) AS INT))

IF @status = 'Ready for Export' AND @sample_request_id > 0 AND @release = 'Yes'
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_sap_request_for_sap_sms') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
IF NOT EXISTS (SELECT sample_request_id FROM mg_sap_request_for_sap_sms WHERE sample_request_id = @sample_request_id and flag = 1 AND sms_type = 2)
BEGIN
INSERT INTO mg_sap_request_for_sap_sms
(
product_id,
sample_request_id,
sample_id,
sms_type,
flag
)
SELECT DISTINCT psr.product_id, psr.request_id, ps.sample_id, 2, 1
FROM pm_sample_request psr
INNER JOIN pm_sample ps ON ps.request_id = psr.request_id
WHERE psr.request_id = @sample_request_id
END
END
END
END

-- Yardage Order Status For SAP
IF @object_id_str !='' AND @object_type_id = 330601 AND @field_id = 100561

BEGIN
SET @status = (
SELECT CASE WHEN COUNT(B.fabric_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
from ft_fabric_sample_request A
INNER JOIN ft_fabric B ON B.fabric_id = A.fabric_id
INNER JOIN ft_fabric_detail FFD ON ffd.fabric_id = B.fabric_id
INNER JOIN ft_fabric_season_year C ON C.fabric_id = B.fabric_id
INNER JOIN ft_fabric_sample_detail D ON A.sample_request_id=D.sample_request_id
LEFT JOIN ft_fabric_colorway_detail E ON D.colorway_id=E.colorway_id

LEFT JOIN (SELECT sample_request_id,location_id AS requested_of_location_id,user_id AS direct_request_to_user_id FROM ft_fabric_sample_requested_to WHERE item_flag = '1' )
F ON F.sample_request_id = A.sample_request_id

LEFT JOIN (SELECT sample_request_id,location_id AS copy_to_location_id,user_id AS copy_request_to_user_id FROM ft_fabric_sample_requested_to WHERE item_flag = '2' )
L ON L.sample_request_id = A.sample_request_id

LEFT JOIN (SELECT sample_request_id,MAX(location_id) AS requested_of_location_id FROM ft_fabric_sample_requested_to WHERE item_flag = '1' GROUP BY sample_request_id) M
ON M.sample_request_id = A.sample_request_id

LEFT JOIN CPM_CompanyLocationInfo G ON G.CPM_CompanyID = M.requested_of_location_id

LEFT JOIN CPM_Company cc ON cc.CPM_CompanyID = G.CPM_CompanyID
LEFT JOIN CPM_company cc2 ON cc2.CPM_CompanyID = cc.CPM_ParentID
LEFT JOIN cpm_companyinfo cc1 ON cc1.CPM_CompanyID = cc.CPM_ParentID

INNER JOIN fb_field_value U1 ON CAST(B.fabric_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = U1.object_id_str AND U1.field_id = 100424 AND U1.object_type_id = 330601 AND U1.module_id = 3 --plant
INNER JOIN fb_field_value U2 ON CAST(B.fabric_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = U2.object_id_str AND U2.field_id = 100425 AND U2.object_type_id = 330601 AND U2.module_id = 3 --storage location
INNER JOIN fb_field_value U3 ON CAST(B.fabric_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = U3.object_id_str AND U3.field_id = 100426 AND U3.object_type_id = 330601 AND U3.module_id = 3 --purchase group
INNER JOIN fb_field_value U4 ON CAST(B.fabric_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = U4.object_id_str AND U4.field_id = 100006 AND U4.object_type_id = 330601 AND U4.module_id = 3 -- Price
INNER JOIN fb_field_value U5 ON B.fabric_id = U5.object_id AND U5.field_id = 100556 AND U5.object_type_id = 3305 AND U5.module_id = 3 -- Tax Code

WHERE

B.current_version = 1
AND CAST(B.fabric_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = @object_id_str
AND cc1.CPM_CompInfoClientCode != ''
AND E.colorway_no != ''
AND D.quantity != 0
and (isnull(dbo.fbf_get_category_item_code(U1.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U2.value_id),'')) != ''
AND U4.value_text != ''
and (isnull(dbo.fbf_get_category_item_code(U3.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U5.value_id),'')) != ''
AND A.need_date != ''
AND A.request_date != ''
AND (isnull(dbo.fbf_get_category_item_code(D.uom),'')) != ''
AND A.request_type_id = 11307
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = @object_id_str AND field_id = 100561

SET @fabric_status = (SELECT TOP 1 ftsy.status_id FROM ft_fabric_season_year FTSY WHERE FTSY.fabric_id = @object_id)

SET @sample_request_id = (SELECT CAST(SUBSTRING (@object_id_str,CHARINDEX(',', @object_id_str) + 1 ,LEN(@object_id_str)) AS INT))

IF @status = 'Ready for Export' AND @fabric_status = 41488 AND @sample_request_id > 0
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_sap_request_for_sap_fabric') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
IF NOT EXISTS (SELECT sample_request_id FROM mg_sap_request_for_sap_fabric WHERE sample_request_id = @sample_request_id and flag = 1)
BEGIN
INSERT INTO mg_sap_request_for_sap_fabric
(
fabric_id,
sample_request_id,
sample_id,
flag
)

SELECT DISTINCT A.fabric_id, A.sample_request_id, D.sample_id, 1
from ft_fabric_sample_request A
INNER JOIN ft_fabric_sample_detail D ON A.sample_request_id=D.sample_request_id
WHERE A.sample_request_id = @sample_request_id
END
END
END
END

-- Sample Order Status For SAP
IF @object_id_str !='' AND @object_type_id = 340401 AND @field_id = 100562

BEGIN
SET @status = (
SELECT CASE WHEN COUNT(A.trim_id) > 0 THEN 'Ready for Export' ELSE 'Not Ready for Export' END as flag
from ft_trim_sample_request A
INNER JOIN ft_trim B ON B.trim_id = A.trim_id
INNER JOIN ft_trim_season_year FTSY ON FTSY.trim_id = A.trim_id
INNER JOIN ft_trim_detail FTD ON B.trim_id = ftd.trim_id
LEFT JOIN ft_trim_sample_detail D ON A.sample_request_id=D.sample_request_id
LEFT JOIN ft_trim_colorway_detail E ON D.colorway_id=E.colorway_id
LEFT JOIN CPM_UserInfo Y ON A.CPM_UserID = Y.CPM_UserID
LEFT JOIN CPM_CompanyInfo Z ON A.CPM_CompanyID = Z.CPM_CompanyID

LEFT JOIN (SELECT sample_request_id,location_id AS requested_of_location_id,user_id AS direct_request_to_user_id FROM ft_trim_sample_requested_to WHERE item_flag = '1' )
F ON F.sample_request_id = A.sample_request_id

LEFT JOIN (SELECT sample_request_id,location_id AS copy_to_location_id,user_id AS copy_request_to_user_id FROM ft_trim_sample_requested_to WHERE item_flag = '2' )
L ON L.sample_request_id = A.sample_request_id

INNER JOIN CPM_CompanyLocationInfo G ON G.CPM_CompanyID = F.requested_of_location_id

LEFT JOIN CPM_Company cc ON cc.CPM_CompanyID = G.CPM_CompanyID
LEFT JOIN CPM_company cc2 ON cc2.CPM_CompanyID = cc.CPM_ParentID
LEFT JOIN cpm_companyinfo cc1 ON cc1.CPM_CompanyID = cc.CPM_ParentID

INNER JOIN fb_field_value U1 ON CAST(B.trim_id AS VARCHAR) = U1.object_id_str AND U1.field_id = 100072 AND U1.object_type_id = 3403 AND U1.module_id = 3 -- Plant
INNER JOIN fb_field_value U2 ON CAST(B.trim_id AS VARCHAR) = U2.object_id_str AND U2.field_id = 100073 AND U2.object_type_id = 3403 AND U2.module_id = 3 -- Storage location
INNER JOIN fb_field_value U3 ON CAST(B.trim_id AS VARCHAR) = U3.object_id_str AND U3.field_id = 100074 AND U3.object_type_id = 3403 AND U3.module_id = 3 -- Purchase group
INNER JOIN fb_field_value U4 ON CAST(B.trim_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = U4.object_id_str AND U4.field_id = 100336 AND U4.module_id = 3 -- Price
INNER JOIN fb_field_value U5 ON CAST(B.trim_id AS VARCHAR)= U5.object_id_str AND U5.field_id = 100558 AND U5.object_type_id = 3403 AND U5.module_id = 3 -- Tax code

WHERE
B.current_version = 1
AND FTSY.status = 1
AND cc1.CPM_CompInfoClientCode != ''
AND E.colorway_no != ''
AND d.quantity != 0
AND u4.value_text != ''
AND a.need_date != ''
AND a.request_date != ''
AND (isnull(dbo.abn_get_category_item_value(d.uom),'')) != ''
AND A.request_type_id = 41655
and (isnull(dbo.fbf_get_category_item_code(U1.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U2.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U3.value_id),'')) != ''
and (isnull(dbo.fbf_get_category_item_code(U5.value_id),'')) != ''
AND CAST( A.trim_id AS VARCHAR) + ',' + CAST(A.sample_request_id AS VARCHAR) = @object_id_str
)

UPDATE fb_field_value SET value_text = @status WHERE object_id_str = @object_id_str AND field_id = 100562

SET @sample_request_id = (SELECT CAST(SUBSTRING (@object_id_str,CHARINDEX(',', @object_id_str) + 1 ,LEN(@object_id_str)) AS INT))

IF @status = 'Ready for Export' AND @sample_request_id > 0
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'mg_sap_request_for_sap_trim') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
IF NOT EXISTS (SELECT sample_request_id FROM mg_sap_request_for_sap_trim WHERE sample_request_id = @sample_request_id and flag = 1)
BEGIN
INSERT INTO mg_sap_request_for_sap_trim
(
trim_id,
sample_request_id,
sample_id,
flag
)

SELECT DISTINCT A.trim_id, A.sample_request_id, D.sample_id, 1
FROM ft_trim_sample_request A
INNER JOIN ft_trim_sample_detail D ON A.sample_request_id=D.sample_request_id
WHERE A.sample_request_id = @sample_request_id
END
END
END

END

END

GO

==================================

i have a same script trigger for update also

Cheers!!!

Nithin

ngirishan
Starting Member

5 Posts

Posted - 2010-02-03 : 06:31:05

i dint used SP because there is a larger possibility for deadlock..

though i wont say this is the most optimised one

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-03 : 07:01:54

What exactly is the business requirement and can you post a sample case?

This is pretty much a perfect example of what not to do with triggers.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ngirishan
Starting Member

5 Posts

Posted - 2010-02-03 : 07:09:11

ok.. lemme start off with teh requirement.

we have a system buit on .net 1.1. and sql server 2000 (i dont have teh source code of the application).

The requirement is to update a field in database at real time basis and if the value is "ready for export", insert few values in to a table so thst data can flow from my application to an erp system.

now the tricky part is that since i dont have the source code, i will have modify any existing procedure of the application(which i am not supposed to do due to the policy). So the only way left to have a trigger on the table to fetch real time data.

Example -

1) Save a record.
2) If
All manadatory data available then "Set field = "ready for export" and insert data in to another table
else
"Set field = "Not ready for export".

Hope this will give you a clear idea

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-03 : 07:11:48

Uuh - that was a hard working devil

Never seen before a trigger like this...

No, you're never too old to Yak'n'Roll if you're too young to die.

ngirishan
Starting Member

5 Posts

Posted - 2010-02-03 : 07:22:11

Hi Sodeep,

i have executed "DBCC TRACEON(1204,-1,3604)" in sql query analyzer. but it retured nothing. i am new to DBCC TRACEON" could you plz giude me on the same.

Cheers!!!

Nithin

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 11:51:04

" SELECT @object_id = ISNULL(object_id,0), @object_type_id = ISNULL(object_type_id,0), @object_id_str = ISNULL(object_id_str,0), @field_id = ISNULL(field_id,0) FROM INSERTED WHERE field_id IN (100533, 100534, 100535, 100560, 100561, 100562)"

Will this trigger work if multiple records are inserted?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources