Whats wrong with Me? Trigger. 1

[VonFranzken]

CREATE TRIGGER tr_insertact
ON technician
FOR INSERT
AS
DECLARE
@tid int,
@eventdate datetime
@artist varchar(255)
@category varchar(10)
@reasonvisit varchar(255)
@postpriority varchar(20)
@activitytype varchar (50)

--get details of inserted row
SELECT @tid = tid,
@eventdate = datetime,
@artist = artist,
@category = category,
@reasonvisit = activitshrtdescrp,
@postpriority = postpriority,
@activitytype = activitytype
FROM inserted
--insert into activity
INSERT activity
SET postpriority = 'Secondary'
SET activitytype = 'AVG'
AND eventdate = @eventdate
AND activityname = @artist
AND category = @category
AND activitshrtdescrp = @reasonvisit
AND pospriority = @postpriority
AND activitytype = @activitytype


END

 

[micha123]

Here you are again....
You missed some characters, and:

CREATE TRIGGER tr_insertact
ON technician
FOR INSERT
AS
DECLARE
@tid int,
@eventdate datetime[highlight] , [/highlight]
@artist varchar(255)[highlight] , [/highlight]
@category varchar(10)[highlight] , [/highlight]
@reasonvisit varchar(255) [highlight] , [/highlight]
@postpriority varchar(20)[highlight] , [/highlight]
@activitytype varchar (50)

[highlight] inserted can contain more than one record! Here you are capturing only the last row! [/highlight]

--get details of inserted row
SELECT @tid = tid,
@eventdate = datetime,
@artist = artist,
@category = category,
@reasonvisit = activitshrtdescrp,
@postpriority = postpriority,
@activitytype = activitytype
FROM inserted

[highlight] You can try my prior advice, instead of the following, since this is not a correct syntax in SQL:
[/highlight]
--insert into activity
INSERT activity
SET postpriority = 'Secondary'
SET activitytype = 'AVG'
AND eventdate = @eventdate
AND activityname = @artist
AND category = @category
AND activitshrtdescrp = @reasonvisit
AND pospriority = @postpriority
AND activitytype = @activitytype


END

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

 

[VonFranzken]

I know I guess what I am asking then is not possible?
I just have a deadline thats all.
Thanks for any help Mike

 

[micha123]

Try this please, and if it does not work - let us know:

create trigger tr_insertact
ON technician
FOR INSERT
as
insert into activity
(eventdate,activityname,category,activitshrtdescrp,
activitytype,postpriority )
select eventdate,artist,category,reasonvisit,
'AVG','Secondary' ---These are strings we are putting in the 2 fields!
from inserted


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

 

[VonFranzken]

Oh Im sorry really but here is the error

Invalid column name 'artist'
Invalid column name 'category'
Invalid column name 'reasonvisit'

These do exist though in the technician table. fyi.

Thanks
Sleepy

 

[micha123]

Can you send us the output of the :
select top 1 * from technician
please, so we would be able to help you reach your deadline.

Thanks.


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

 

[VonFranzken]

tid eventdate artist bio category targetartist instrument manufacturer model affiliation reasonvisit actiontaken future techinfo notes image1title image1 image2title image2 image3title image3 image4title image4 audiotitle audio videotitle video datenow

Cant post the data its confidential but will this help?
Artist info for example dont want it googled.

 

[micha123]

Is it possible that you have more than this trigger on the table (because the trigger seems to be OK.
Please check if I'm correct.


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

 

[VonFranzken]

Well what I do was check the syntax and that is when it said the above error about columns, should I just try to run it or should the syntax check be a stop warning that there is an error?

Do we have to tell the inserted about the 2 feilds it doesnt know about?

Steve

 

[micha123]

inserted is a local temporary table in a trigger and it has the same structure as the table the trigger is on.
So...
I have read this trigger over and over again and I don't see anything wrong.
Maybe someone else would understand the problem.
I can not see why the trigger has a compilation error.
I havn't tested it, but maybe a begin and an end would solve the problem...?
or maybe a dbo before the table name?

create trigger tr_insertact
ON dbo.technician
FOR INSERT
as
BEGIN
insert into dbo.activity
(eventdate,activityname,category,activitshrtdescrp,
activitytype,postpriority )
select eventdate,artist,category,reasonvisit,
'AVG','Secondary'
from inserted
END






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

 

[VonFranzken]

Mike it works like a charm and thank you.

Sorry for the confusion, seems my local database at work was what the problem was I tried it at home and walla.
Thanks again for all your help
Steve