Help with Trigger

[VonFranzken]

I need to create a trigger that will insert a record into another table, here is the criteria. I want so that everytime a new technician report is added it gets also recorded in teh activity table as a aritist visit guest book item:

technician (table source) activity(table destination)
eventdate ---------------------->eventdate
artist-------------------------->activityname
category------------------------>category
reasonvisit--------------------->activitshrtdescrp
activitytype = 'AVG'------------>activitytype
postpriority = 'Secondary'------>postpriority

But also insert which does not exist in the technician table but does in the activities table the feilds activitytype = 'AVG' (for artist visit guestbook)
postpriority = 'Secondary' (for report posting priority)

thanks so much for any help with this.

Steve

 

[micha123]

create trigger new_trg on technician
for INSERT
as
insert into activity
(eventdate,activityname,category,activitshrtdescrp,
activitytype,postpriority )
select eventdate,artist,category,reasonvisit,
'AVG','Secondary' - I am not sure I understood correctly...
from inserted

can you please explain:
"But also insert which does not exist in the technician table but does in the activities table the feilds activitytype = 'AVG' (for artist visit guestbook)"
?

M.

 

[VonFranzken]

Sure and thanks for your help.

technican does not have a feilds called activitytype or postpriority, but I need to insert default values of 'AVG' for activitytype and 'Secondary' for postpriority feilds that do exist in the activity table every time an insert is made to the activity table.

So basically we are inserting 6 feilds from techncian insert 2 of which do not get inserted into the technician table and 6 feild into the activity table.

Does that make sense?

thanks again.
steve

 

[micha123]

Nop....
Did I get it right or - please explain exactly what are 'AVG' and 'SECONDARY' (with examples).


M.

 

[VonFranzken]

Sure and again thank you, Ok your on the techncian report insert form you are inserting 9 feils 4 of which are eventdate, artist, category, reasonvisit, which need to be inserted into the activity table as eventdate, activityname, category, activitshrtdescrp, acititytype, postpriority. The last two activitytype and postpriority are not part of the original technician insert, so I guess they would have to be added only in the trigger.

Does this make sense?
Steve

 

[micha123]

Can you give me an example, pleas?

 

[VonFranzken]

create trigger new_trg on technician
for INSERT
as
insert into activity
(eventdate,activityname,category,activitshrtdescrp,
activitytype,postpriority )
select eventdate,artist,category,reasonvisit,

I think we just need a couple of Sets like below since they dont exist in the inserted. Where in your statement can we put the Set statements?

Like SET postpriority = 'Secondary'
SET activitytype = 'AVG'

When you say example what do you mean?

 

[VonFranzken]

We need to set default values for the postpriority and activitytype.

does that make sense?

 

[micha123]

This is what I have done!
Did you try it?


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.