Hello!

I want to create an update trigger on table "A". What I want is to
udpate the "status" and "submit_date" flds in table "A" if it meets the
criteria. See below the udpate statment which works. I dont know how to
change this code to work for Trigger?.How  do I do that? I guess what I
am asking is how to use Inserted (or Deleted) table in this query?

Thank you!

create trigger tiu_A on [dbo].[A] 
for update as
begin

update    A
set
status = 'Complete',
submit_date = dateadd(day, 30, b.create_date)
from    A a inner join B b
on    a.ID = b.ID
where    b.Type = 'DOCUMENT' AND
    a.status = 'On Request'
end




*** Sent via Developersdex http://www.developersdex.com ***

Please post DDL!

or

I suggest you look up 'triggers, programming' in Books On Line.
Refer to COLUMNS_UPDATED  for more info.


Show quoteHide quote

Here you go!

create table dbo.A
(ID int,
status varchar(30),
submit_date datetime)

insert into A
values(1, 'On Request', '1/1/2005')

insert into A 
values(2, 'On Call', '10/10/2005')


create table dbo.B
(ID int,
Type varchar(30),
create_date datetime)

insert into B
values(1, 'DOCUMENT', getdate())

insert into B
values(1, 'FILE', '10/28/2005')








*** Sent via Developersdex http://www.developersdex.com ***

Per your specifications:

create table dbo.A
(ID int,
status varchar(30),
submit_date datetime)

insert into A
values(1, 'On Request', '1/1/2005')

insert into A 
values(2, 'On Call', '10/10/2005')


create table dbo.B
(ID int,
Type varchar(30),
create_date datetime)

insert into B
values(1, 'DOCUMENT', getdate())

insert into B
values(2, 'FILE', '10/28/2005')
--** changed id value from 1 to 2

--*******************************************************************************

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'upD_A_status' AND type
= 'tr')
DROP TRIGGER upD_A_status
GO

CREATE TRIGGER upD_A_status
ON a
FOR UPDATE
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
BEGIN
SET NOCOUNT ON
  DECLARE @type  VARCHAR(30)
  DECLARE @status VARCHAR(30)

  SELECT @type = type
  FROM B
  WHERE id = (SELECT id FROM deleted) -- can also use inserted

-- on update the orginal data can be queried from the virtual table 'deleted'
-- and the revised data can be queried from the virtual table 'inserted'

  SELECT @status = status
  FROM inserted

  IF @status = 'On Request' AND @type = 'DOCUMENT'
    Update a
    SET a.status = 'Complete', a.submit_date = DATEADD(day, 30,
b.create_date)
    FROM a INNER JOIN b ON a.id = b.id
END
GO
--*****************************************************************************

--test data, should yield no effect
UPDATE a
SET status = 'test'
WHERE id = 1

--test data, trigger should meet your specifications
--UPDATE a
--SET status = 'On Request'
--WHERE id = 1


Show quoteHide quote

Dave,

I appreciate your help. I tested your code but it is updating the status
of both rows in A.

--test data, should yield no effect
UPDATE a
SET status = 'test'
WHERE id = 1

--test data, trigger should meet your specifications
UPDATE a
SET status = 'On Request'
WHERE id = 1

I am getting this:

ID     status   submit_date
1      Complete  2005-12-14 10:01:12.950
2      Complete  2005-11-27 00:00:00.000

The ID 2 shuld not be affecting bc if it not meeting the requirements
i.e type = 'document' and  status = 'on request'.

Thanks for your help!






*** Sent via Developersdex http://www.developersdex.com ***

convert INT
A difficult Combining Rows problem
How to use OPENXML to retrieve detail lineitems
what is general/conventional way to reduce/prevent deadlock?
Complicated OR