Creating Triggers

[christine1]

Good morning,

I'm trying to create an Insert/Update trigger (see trig below)Here's what I want to do: If the record does not exist in the inserted table then insert into cube_control_tbl, if it does and the record has changed then update. What am I doing wrong???

Please help I have to have this by noon.

Signed brain fried

CREATE TRIGGER manual_control_trig ON dbo.CUBE_CONTROL
FOR INSERT,UPDATE
AS

IF UPDATE

(select * from header_view hi, inserted i
where hi.phys_piece = i.phys_piece)

DECLARE
@company company,
@site site,
@plant plant,
@design design,
@serial_num serial_num,
@phys_piece phys_piece,
@last_ops last_ops,
@manual_control manual_control,
@last_rep_date last_rep_date


INSERT INTO CUBE_CONTROL
(COMPANY, SITE, PLANT, DESIGN, UNIT, SERIAL_NUM, PHYS_PIECE, LAST_OPS,
MANUAL_CONTROL, LAST_REP_DATE)
VALUES (@COMPANY, @SITE, @PLANT, @DESIGN, @UNIT, @SERIAL_NUM, @PHYS_PIECE, @LAST_OPS,
@MANUAL_CONTROL, @LAST_REP_DATE)


UPDATE CUBE_CONTROL
SET i.last_rep_date = i.last_ops
FROM header_view hi, inserted i
WHERE i.manual_control = 'N'
AND hi.phys_piece = i.phys_piece

GO [sig][/sig]

 

[gamcall]

Christine,
For a start, I'd get the declarations done before
anything else - that's just good programming. Also, I
thought that "if update" only applied (specifically)to a
field, 'tho I'm not sure. Also, you said you wanted an
insert into cube_control table, but your insert goes into
cube_control_tbl. Are these meant to be the same?

GAM [sig][/sig]

 

[christine1]

Okay, so you are saying declarations should go first (move that select statement)? Yes, cube_control is the table not cube_control_tbl (sorry about that)

You are correct about the IF UPDATE that's why I cannot get that to work, but it has the concept I'm looking for. I still cannot get it to work. Please HELP!!! [sig][/sig]

 

[gamcall]

Just what _exactly_ are you trying to do? What table do
you want to detect the update/insert on & then which table
do you want to insert/update? At the moment, it looks as
'tho the answer to both is cube control, & that doesn't make sense to me. Sorry, I need a bit more clarification.

GAM [sig][/sig]