• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

creating trigger to change value of inserted

I am trying to create a trigger that fires when a record is inserted in table SUB. It should check if the value of SUB.itype is either 1, 2 or 3. If yes, the go to table MAIN and check value of iname. If MAIN.iname LIKE "Toyota%" then change value of Inserted SUB.itype to 0. (SUB.rec_id = MAIN.rec_id).

This is what I have:

CREATE TRIGGER TypeTrigger ON SUB FOR INSERT AS
DECLARE @my_itype VARCHAR(1)

SELECT @my_itype = (SELECT iname FROM MAIN WHERE(rec_id IN (SELECT rec_id FROM Inserted WHERE (itype > 0))) AND (MAIN.iname LIKE "Toyota%"))

IF @my_itype > 0

UPDATE Inserted
SET itype = 0

This is where Im stuck - what do I do?

Thks - CC

0
cc258
Asked:
cc258
  • 2
1 Solution
 
HilaireCommented:
You can't update the "inserted" virtual table. It's read-only.
Instead, you can update the SUB table directly. But you need to have a primary key/unique identifier in this table to update only the relevant records

CREATE TRIGGER TypeTrigger ON SUB FOR INSERT AS
UPDATE S
Set itype = 0
FROM SUB S
INNER JOIN INSERTED I ON S.primarykey = I.primarykey  -- is rec_id the primary key ??
INNER JOIN MAIN M on M.rec_id = I.rec_id
where S.itype > 0
and M.iname like 'Toyota%'

HTH
Hilaire
0
 
cc258Author Commented:
Looks fine - I will give it a spin.

rgds CC
0
 
arbertCommented:
You can use INSTEAD OF triggers to update the data directly....
0
 
cc258Author Commented:
Thanks - it worked ok.

rgds
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now