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

SQL Server 2005 Forums

Transact-SQL (2005)

trigger

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-03 : 15:42:17

I would like to add a trigger.

when ever there is an insert on Order table then it will insert the same record to another database (FillOrder) table (tblFillOrders) with orderid, orderprice, quantity.

how? never written trigger before.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 15:49:12

Check out CREATE TRIGGR in BOL first. Check out the examples and then come back to use with any questions.

Hint: you'll want to reference the inserted trigger table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-03 : 15:51:16

Yea, I did that and but not sure they included my scenario. Thanks Tara

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-03 : 15:59:48

something like this...

CREATE TRIGGER [Trig_Order_Insert]
ON [tblOrder]
FOR INSERT
AS
BEGIN
	INSERT INTO tblFillOrders
		(
			orderid,
			orderprice,
			quantity
		)
	SELECT
			orderid,
			orderprice,
			quantity
	FROM Inserted
END

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-03 : 16:12:08

1. vijay, tblFillOrders is in another database called "FillOrder". However, they are on same server.

2. I dont want the transaction go through if there is an error. How do I handle errors ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 16:45:08

INSERT INTO FillOrder.dbo.tblFillOrders
...

IF @@ERROR <> 0
ROLLBACK TRAN

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-03 : 16:59:07

Thanks Vijay and Tara for the great info and the code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-03 : 17:02:32

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-04 : 09:31:46

final verification. Am I missing something? or this is as good as it gets. Will my sql statement work for insert? and will the try and catch work. Is the rollback in right place?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-04 : 11:02:31

You do not need a transaction for this as you are already in a transaction when the trigger fires.

Your trigger is incorrect though as it isn't using the inserted trigger table. Check vijay's solution again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-08 : 15:25:05

I am still confused on the "FROM INSERTED" LINE cause my from looks different. I have 5/6 outer table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-08 : 16:07:24

I don't understand what you mean.

When you run a trigger, you have access to the inserted and deleted trigger tables. For inserts, you'll only have data in the inserted table. For updates, you'll have the "before" data in the deleted table and the "after" data in the inserted table. For deletes, you'll only have data in the deleted table.

Since you are using only an INSERT trigger, you just need to query the inserted trigger table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-09 : 11:05:14

USE [myDATABASE]
GO
/****** Object: Trigger [dbo].[it_myTrigger] Script Date: 12/09/2009 07:45:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[it_myTrigger]
ON [dbo].[Dodge]
FOR INSERT
AS
DECLARE @runDateTime datetime,
@UserName varchar(100)
set @runDateTime = GETDATE()
set @UserName = system_user
BEGIN TRY
-- INSERTING RECORD INTO ANOTHER DATABASE
INSERT INTO [2NDDATABASE].[dbo].[TABLE1]
(ID,
LName,
FName,
MName,
Suffix,
DOB,
Gender,
STATUS,
DEPARTMENT,
ETHNC,
DOH,
DOL,
AFTERStatus,
DOR,
BEFORESTATUS,
CURRENTGRADE)
SELECT
d.id,
d.lname,
d.fname,
d.middle,
(Select n.desc from dbo.suffix as n where (n.id = d.nsid)) as N,
d.dob,
d.gdr,
(select s.desc from s where (s.id = d.sid)) as s,
(select se.desc from se where (se.id = d.seid)) as seDesc,
(select sc.abbr from sc where (sc.id = d.scid)) as Abbrev,
(select r.desc from r where (r.id = d.rcid)) as r,
d.doh,
(select p.desc from p where (p.id = d.pid)) as pdsc,
d.dor,
bs = (select b.desc from dbo.bs as b where(b.bid = d.bid),
apcDesc = (select a.desc from dbo.trocode a where (a.id = d.aid)
FROM INSERTED d

-- Send email Regarding this transaction
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'recipient@gmail.com',
@body = 'A record with id ' + d.id + ' has been updated',
@subject = 'Subject: Database Mail'

END TRY

BEGIN CATCH
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
ROLLBACK -- ROLLBACK THE TRANSACTION
END
INSERT INTO [2ndDataBase].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
'table1',
'Insert',
'Attempt Failed')
END CATCH

==========================================================================

I am getting to many errors...

Msg 156, Level 15, State 1, Procedure it_myTrigger, Line 39
Incorrect syntax near the keyword 'where'.
Msg 156, Level 15, State 1, Procedure it_myTrigger, Line 41
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Procedure it_myTrigger, Line 43
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure it_myTrigger, Line 45
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Procedure it_myTrigger, Line 51
Incorrect syntax near '+'.

also, "FROM INSERTED" THIS line gives error

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-09 : 12:15:51

Still the same error. I took out all the lookup statement (select * from watever wehre id-id) and still the same.

------------------------*/
Msg 156, Level 15, State 1, Procedure it_myTrigger, Line 46
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Procedure it_myTrigger, Line 53
Incorrect syntax near '+'.

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-09 : 12:28:02

It's now working except the last concatenate piece for the email body.

Msg 102, Level 15, State 1, Procedure it_myTrigger, Line 53
Incorrect syntax near '+'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-09 : 12:35:07

You are referring to tables that don't exist in your trigger, such as r, se, sc, and p.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-09 : 12:35:41

Show us your new code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-09 : 12:37:58

@body = 'A record with id ' + convert(varchar(50), d.id) + ' has been updated.',

I am getting this error
Incorrect syntax near '+'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-09 : 12:51:16

d.id is not in the context of the query anymore, so you can't refer to it there. The query has already been completed. You'll need to instead use a variable there to avoid the syntax issue.

@body = @v1,

So somewhere above in the trigger, you'll need to populate @v1.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-09 : 13:13:44

Great. Learned a lot. Tara, it's now working. Thanks for all your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-09 : 13:20:54

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources