Writing a Trigger

[blackbirdMIT]

I need help on how to provide an INSERT statement to activate a trigger given the tables below:

MYOILS Table
OilID - PK, int
OilName - nvarchar
LatinName - nvarchar, allow nulls
PlantTypeID - int, allow nulls
PlantPartID - int, allow nulls
Sample - char, allow nulls
Description - varchar, allow nulls


OILS Table (being the master table)
OilID -
OilName
LatinName
PlantTypeID
PlantPartID

My goal: after I add a new oil into the Oils table, it should automatically update the MyOils table with the new oil I added.

Please help!

 

[ClaireHCM]

Your Oils(OilId) shouldnt set as primary key,and all the field in Oils table shouldnt allow null.Otherwise,even if you activate the trigger ,it's going to be some null value exists in MyOils table


create trigger tr_iOils on Oils for insert as
update MyOils set
OilName = (select OilName from inserted),
LatinName = (select LatinName from inserted),
PlantTypeId = (select PlantTypeId from inserted),
PlantPartId = (select PlantPartId from inserted),
Sample = (select Sample from inserted),
Descript = (select Descript from inserted)

 

[blackbirdMIT]

The MyOils table did not update after I added a test oil into the Oils table using INSERT statement.

INSERT INTO Oils
VALUES ('50', 'OilNameTest1', 'LatinNameTest1', '1', '1')
GO

What am I missing?

 

[ClaireHCM]

I am sorry,please update to the following query

update MyOils set
OilName = (select OilName from inserted),
LatinName = (select LatinName from inserted),
PlantTypeId = (select PlantTypeId from inserted),
PlantPartId = (select PlantPartId from inserted),
Sample = (select Sample from inserted),
Descript = (select Descript from inserted)
where OilId = (select OilId from inserted)

BTW,Do u have a OilId = 50 in MyOils table?

 

[blackbirdMIT]

It is still not updating the MyOils table after adding the

where OilID = (select OilID from inserted)

OilID = int(4) in MyOils table

 

[blackbirdMIT]

The MyOils table is empty; no records yet.

 

[ClaireHCM]

If no record ,how you are going to update?
Only if you have record and want to change the existing data ,it's called update.

do you want to trigger a row insert into MyOils whenever rows are inserted into Oils?


--This is the insert trigger
create trigger tr_iOils on Oils for insert as
insert into MyOils(OilId,OilName,LatinName,PlantTypeId,PlantPartId)
select * from isnerted

 

[blackbirdMIT]

How can I expand the scope of the trigger to handle delete and update for the insert trigger you've shown above?

 

[ClaireHCM]

I already gave you the update trigger

For delete trigger:

Create Trigger tr_dOils for delete as
delete from MyOils where OilId = (select oilId from deleted)


Remember,without any records in MyOils table.It's not possible to activate the Delete and Update action

 

[JamesLean]

Here are some basic templates of triggers which will maintain a copy of a table, similar to what you are doing here. This assumes both tables are identical and have the same columns etc. These triggers will also handle multiple inserts/updates/deletes. Hopefully you can use the theory and fit it into your solution:

table1
id
name
description

table2
id
name
description

--insert
CREATE TRIGGER tr_table1_ins
  ON table1
  FOR INSERT
AS

INSERT table2 (id, name, description)
SELECT id, name, description
FROM inserted
GO

--update
CREATE TRIGGER tr_table1_upd
  ON table1
  FOR UPDATE
AS

UPDATE t2
SET name = i.name,
  description = i.description
FROM table2 t2 JOIN inserted i ON t2.id = i.id
GO

--delete
CREATE TRIGGER tr_table1_del
  ON table1
  FOR DELETE
AS

DELETE table2
FROM table2 t2 JOIN deleted d ON t2.id = d.id
GO

--James