Update Trigger... best solution

Update Trigger... best solution

• Mudasser

  Thanks Jens you have been more than helpfull
  

• benwestgarth

  Jens

  Thanks for the responce, I am still getting an error:

  Server: Msg 208, Level 16, State 1, Line 1
  Invalid object name 'deleted'.

  primary key on the table is called id, so the query looks like this:

  update customer
  set deleted = (case when (account_status = 4 OR account_status = 6) THEN 1 WHEN account_status = 5 THEN 0 ELSE deleted END)
  FROM customer c
  INNER JOIN DELETED d
  ON d.id = c.id

  Any ideas

  Thanks... Rich

  
  

• ashk1860

  Look up "nested triggers" (and "recursive triggers")in BOL. It is a setting that you most likely will need to turn off or manage by checking the value of @@nestlevel and/or TRIGGER_NESTLEVEL. If you update the same table it can cause the trigger to fire and re-update the table and so on and so on :)

  In your case, you probably want to stop nesting after the first iteration...

  
  
  

• Cat the Dark

  I guess there is always an index on the id column, so there is not much left for me to optimize in there, though this is a real simple statement.

  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---

  
  

• renju_dom

  Jens / Louis thanks you for both of your help... it works ....

  my next problem it is very slow, it takes about 1 minute to execute

  create trigger TEST_UPDATE
  on customer
  after update
  as

  if update (account_status)

  begin

  update customer

  set deleted = (case when (c.account_status = 4 OR c.account_status = 6) THEN 1
  WHEN c.account_status = 5 THEN 0 ELSE c.deleted END)
  FROM customer c
  INNER JOIN DELETED d
  ON d.id = c.id

  end
  

  can i speed it up

  
  

• Garrett Fitzgerald

  
  First of all your query make only partly sense:

  where account_status = 4 and account_status = 6

  The account status can’t be 4 AND 6 at the same time :-), guess you ment OR instead of AND.

  update customer
  set deleted = (case when (account_status = 4 OR account_status = 6) THEN 1 WHEN account_status = 5 THEN 0 ELSE deleted END)
  FROM customer c
  INNER JOIN DELETED d
  ON d.PrimaryKeyColumnhere = c.PrimaryKeyColumnhere

  HTH, Jens Suessmeyer.

  ---
  http://www.sqlserver2005.de
  ---

  
  

• Dim

  Thanks for the reply, i have set up the trigger all ok.... when i update the account_status in the crm software I am getting this error:

  The Article contains invalid or missing data

  Error(s) occurred when executing UPDATEError [-2147217900][microsoft][ODBC SQL server driver][sql server]maximum stored procedure, function, trigger or view nesting level exceeded (limit 32)(microsoft ole db provider for odbc driver)

  sorry to be a pain but i am really lost!!

  
  

• Khalid K.

  One thing to note, if you are going to always join to the DELETED table (in the trigger, as Jens notes) then no need to do this in a INSERT trigger as the deleted table will always be empty:

  create table test
  (
  testId int
  )
  go
  create trigger test$insertUpdate
  on test
  after insert,update
  as
  select *
  from inserted
  select *
  from deleted
  go
  insert into test values (1)

  
  
  

• Mike Wasson-MSFT

  The deleted table is onyl accessible within a trigger, so executing the statement in query analyzer will always come up with an error. Within an Update / Deleted trigger the table should exists, although it might be empty because trigger are foried for statements not for rows, so even if an update / delete affects 0 rows, the trigger will fire.
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  
• Update Trigger... best solution