Problem withe below trigger

Question

hi All,

I dont know what is the problem with the below mentioned trigger. can anyone point out the same to me.

ALTER TRIGGER [dbo].[Update_Trigger]
    ON [dbo].[FileReport_Consolidated]
FOR INSERT
AS
BEGIN

update dbo.FileReport_Consolidated SET dbo.FileReport_Consolidated.Resolved=1  where dbo.FileReport_Consolidated.FilePath IN (SELECT A.FilePath from dbo.FileReport_Consolidated AS A LEFT OUTER JOIN dbo.FileReport_Recent AS B ON A.FilePath = B.FilePath where B.FilePath IS NULL)

END

I do not have clarity about when  the trigger is initiated

1) after the table level insertion or

2) row level insertion

Answer 1

As this is not an AFTER INSERT trigger it will fire before the insert occurs.

Answer 2

As this is not an AFTER INSERT trigger it will fire before the insert occurs.

HI !

Stephen FOR / AFTER triggers are same in MS SQL server.

Hi Vjy !

You may have a look at below MSDN article for your reference;

http://msdn.microsoft.com/en-us/library/ms191524%28v=sql.105%29

Also trigger executes per batch.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Note : If you are having any error please try to post the exact error message.

Thanks, Hasham Niaz

Answer 3

Also, this occurs as both row level and table level insertion, although technically it is "table level".  ALL triggers must be coded such that they can handle the manipulation -- in this case insertion -- of multiple rows.  Here are some previous posts that discuss the need to be able to accommodate muti-row triggers:

Trigger, multi-row:

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/08b54a2c-4c8f-4ecf-af19-359201751362/
      Umachandar Jayachandran
      Louis Davidson

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/f3cc9a80-73a5-450b-b01e-7ac5f4bb44d5
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/3e24c99f-29c8-480c-9c74-5bf267485cbc/
   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/b72a540f-40e8-40c5-b00c-9bdd89798103
      Umachandar Jayachandran

   http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/be83aca9-68bf-41fb-82df-6790e00ea739/
      Jim McLeod
      Kent Waldrop

   http://www.jimmcleod.net/blog/index.php/2008/06/05/triggers-set-based-not-row-based/
      Jim McLeod

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/8211069f-e4cd-48d3-b63c-93ac4bb3c6de
      Kent Waldrop

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/5b041698-b102-4202-a7f4-87ed4747faee/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8172c7ea-4e89-4bcc-a923-0f04deb83b65/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/cbd6c093-143a-46cd-a65f-263694f58c38/
      Kent Waldrop

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/5533449e-0dfc-4f56-a6b5-01bc62e2ec97/
      Kent Waldrop
      Dennis Gobo
      Madhu Nair

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/c309e6b9-0c0a-4550-aa57-f9a86c469813/
      Arnie Rowland
      Kent Waldrop
      Jonathan Kehayias
      David Dye

   http://social.technet.microsoft.com/Forums/en-US/sqlgetstarted/thread/2a89046b-8a67-4f86-b9b2-c92ea4a37f66
      Kent Waldrop
      Naomi Nosonovsky

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/c1557eec-747d-491b-9356-447a736f5174
      Naomi Nosonovsky

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/e80d63b1-a55d-4510-9ee4-2c1959cdac33
      Stefan Hoffman
      Kent Waldrop

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/fa21795f-ea03-45d0-8cc7-0816fdfaafc2
      eralper
      Kent Waldrop

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/143ef163-b0c6-456d-b71e-c2fe0afed4ea
      Naomi Nosonovsky
      Kent Waldrop

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/50f33df8-a9b2-4cc0-ae62-7a4576e8100b
      Neils Berglund

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/a4db698b-af6a-4831-b311-dcacd333db20
   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/7098ed05-d850-4674-89b8-392faa4bf10d
      Jens Suessmeyer

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/a2aca162-63f8-4de6-8cf0-4a87e26e3e9e
      Kent Waldrop

   Multi-Row for Instead Of Trigger:
      http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/beb62c8f-d54d-4c34-93ed-df063a8f4034
         Louis Davidson
         KH Tan