| Author |
 Topic  |
|
|
egemen_ates
Yak Posting Veteran
Turkey
63 Posts |
 Posted - 06/22/2012 : 05:59:00
|
I create trigger my trigger show belown as ,some time update statement given deadlock for orders table.how can i solve this case
CREATE TRIGGER [dbo].[add_orders] ON [dbo].[ORDERS] INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
SET NOCOUNT ON;
IF EXISTS(SELECT TOP 1 ORDER_ID FROM INSERTED)
BEGIN
IF EXISTS(SELECT TOP 1 ORDER_ID FROM DELETED)--UPDATE
BEGIN
DELETE FROM workcube_cf_2012_1.dbo.ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM INSERTED)
INSERT INTO
dbo.ORDERS_VIRTUAL
(
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
)
SELECT
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
FROM
INSERTED
END
ELSE--INSERT
BEGIN
INSERT INTO
dbo.ORDERS_VIRTUAL
(
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
)
SELECT
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
FROM
INSERTED
END
END
ELSE
BEGIN
IF EXISTS(SELECT TOP 1 * FROM DELETED)--DELETE
BEGIN
DELETE FROM dbo.ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM DELETED)
END
END
SET NOCOUNT OFF
END; |
|
|
namman
Constraint Violating Yak Guru
USA
259 Posts |
Posted - 06/22/2012 : 16:28:21
|
Fixing deadlock is time consumming, and we need more info than that
Could you post the trace file of that deadlock?
Also, have a quick question.
For Update, you delete rows from table ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM INSERTED). Then you insert into the same table with data, and the same ORDER_ID ?
If so, why don't you just update the table. Do I miss something. |
 |
|
| |
Topic |
|
|
|
DeadLock
