0

Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?

In my case, I am using a trigger to read from two view tables (Decisions2 and Con_Decisions_Groupby), each table contains more than 20000 records, and only I want the trigger to work with the updated values and if a new record is added to the view table, and not to go through all the records in order to hide the delay. This is my trigger: (I am not sure if it is written correctly to cover my case)

USE [W1]
GO
/****** Object:  Trigger [azteca].[trg_WOcommittee_02]    Script Date: 6/5/2018 10:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [azteca].[trg_WOcommittee_02]
   ON [azteca].[WORKORDER]
   AFTER UPDATE, insert
AS 
   UPDATE [azteca].[REQUEST] 

SET [azteca].[REQUEST].[TEXT11] = [dbo].[Decisions2].[L1_Desecion], 
    [azteca].[REQUEST].[TEXT12] = [dbo].[Decisions2].[L2_Desecion],
    [azteca].[REQUEST].[TEXT13] = [dbo].[Decisions2].[R1_Desecion],
    [azteca].[REQUEST].[TEXT14] = [dbo].[Decisions2].[R2_Desecion],
     [azteca].[REQUEST].[TEXT4] = [dbo].[Decisions2].[HPC1_Desecion],
    [azteca].[REQUEST].[TEXT15] = [dbo].[Decisions2].[SPC2_Desecion], 
    [azteca].[REQUEST].[TEXT5] = [dbo].[Decisions2].[Drafting],
    [azteca].[REQUEST].[TEXT2] = [dbo].[Decisions2].[Auditing_Desecion],

        [azteca].[REQUEST].[TEXT20]= [dbo].[Con_Decisions_Groupby].[TEXT20]


FROM [azteca].[REQUEST]  FULL JOIN [dbo].[Decisions2] ON [azteca].[REQUEST].[REQUESTID] = [dbo].[Decisions2].[REQUESTID] 
                       Full join [dbo].[Con_Decisions_Groupby] on [azteca].[REQUEST].REQUESTID =  [dbo].[Con_Decisions_Groupby].REQUESTID

Kindly find the attached screen shot which shows all the story: enter image description here

enter image description here

4
  • Please read the accpted answer here and also this and this. Take the tour and visit the help desk to see what's on- and off-topic for this site - you're OK on this score, your question is on-topic! p.s. welcome to the forum! :-)
    – Vérace
    Jun 5, 2018 at 8:31
  • You'll need to join to the inserted table to limit updates to the affected rows. There is no mention of the azteca.WORKORDER table in your trigger code so I'm guessing table is used in the views. You should add the CREATE VIEW DDL to your question. The FULL JOINs with no WHERE clause look odd and the screen shots don't tell any story I understand.
    – Dan Guzman
    Jun 5, 2018 at 12:05
  • Thank you Dan, really appreciate your answer, the view table is reading from the azteca.workorder table and when adding the trigger to the view table, the trigger is not working, Sorry for the confusion, [lease find the code of the view tables:
    – Majdoleen
    Jun 6, 2018 at 6:49
  • "You'll need to join to the inserted table to limit updates to the affected rows" - @DanGuzman Jun 6, 2018 at 13:18

1 Answer 1

0

Sorry for the confusion, the view table is reading from the azteca.workorder table and when adding the trigger to the view table, the trigger is not working, please find the code of the view tables: [dbo].[Decisions2]>>>

SELECT        REQUESTID, Communities, ObjID, [L1] AS [L1_Desecion], [L2] AS [L2_Desecion], [R1] AS [R1_Desecion], [R2] AS [R2_Desecion], [HPC1] AS [HPC1_Desecion], [HPC2] AS [SPC2_Desecion], [Drafting] AS [Drafting], 
                     [Auditing] AS [Auditing_Desecion], Attachments, PROBLEMSID

FROM (SELECT TEXT1, REQUESTID, Communities, ObjID, Attachments, COMMENTS, PROBLEMSID FROM dbo.RequestToWorkorder) src PIVOT (max(TEXT1) FOR COMMENTS IN ([L1], [L2], [R1], [R2], [HPC1], [HPC2], [Drafting], [Auditing])) piv;

[dbo].[Con_Decisions_Groupby]>>>

SELECT        MAX(REQUESTID) AS REQUESTID, TEXT20

FROM dbo.Con_Decisions GROUP BY REQUESTID, TEXT20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.