After insert trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

After insert trigger

Author

Topic

Cheryl01
Starting Member

5 Posts

Posted - 2008-11-25 : 16:12:10

Hi, I'm trying to create a trigger that inserts a new record into tblService with the QuoteID from tblServiceQuote when tblServiceQuote.Accepted = true or = 1( it's a bit).. Anyway, I'm struggling and I'm wondering if someone can tell me where I'm going wrong..

CREATE TRIGGER servicequote

AFTER update servicequote
When(ServiceQuote.Accepted=1)
BEGIN
-- Insert record into Service table
INSERT INTO service
( ServiceQuote.quoteid
)
VALUES
( : quoteid,
);
END;

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 16:29:19

Did you mean this?
CREATE trigger [trg_servicequote] on [dbo].[tblServiceQuote]
After insert
as
begin
insert into dbo.tblService
select i.* from dbo.tblServicequote tb
inner join inserted i
on i.Quoteid = tb.Quoteid
where i.accepted = 1
end

Cheryl01
Starting Member

5 Posts

Posted - 2008-11-25 : 16:47:56

Thanks that was fast.. Question.. What is i referring to? And I only want to send the QuoteID from tblServiceQuote to tblService.. Does this do this? Also I'm getting this error message:cannot use text,ntext or image colums in inserted and deleted tables..an explicit value for the identity column in table dbo.tblservice can only be specified when a column list is used and IDENTITY INSERT is on...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:05:30

quote:
Originally posted by Cheryl01

Thanks that was fast.. Question.. What is i referring to? And I only want to send the QuoteID from tblServiceQuote to tblService.. Does this do this? Also I'm getting this error message:cannot use text,ntext or image colums in inserted and deleted tables..an explicit value for the identity column in table dbo.tblservice can only be specified when a column list is used and IDENTITY INSERT is on...

for text,ntext etc try casting to varchar(max),nvarchar(max) if you're using sql 2005.
for passing explicit value for identity use this before

SET IDENTITY_INSERT yourtablename ON
---yourcode
SET IDENTITY_INSERT yourtablename OFF

Cheryl01
Starting Member

5 Posts

Posted - 2008-11-29 : 15:48:41

Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 01:09:35

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources