December 8, 2009 at 1:18 pm
Writing my first trigger. Can anyone verify or fix if I am missing anything? Many thanks in advance...
CREATE TRIGGER [it_Orders_to_OrdersFill]
ON [Orders]
FOR INSERT
AS
BEGIN TRY
INSERT INTO [DB2].[dbo].[OrdersFill]
(OrderID,
LastName,
FirstName,
MiddleName,
Birthday,
Gender,
Class,
Agency,
Race,
Status)
SELECT d.id,
d.lastn,
d.firstn,
d.mn,
n.DESC,
d.dob,
d.gender,
scc.DESC AS sccDesc,
sac.cyabbrev,
rc.DESC AS rcDesc,
pac.DESCRIPTION AS pacDesc
FROM dbo.Orders d
LEFT OUTER JOIN dbo.fixcode n
ON d.fixid = n.fixid
LEFT OUTER JOIN dbo.cycode ssc
ON d.cyid = ssc.cyid
LEFT OUTER JOIN dbo.tecode sac
ON d.teid = sac.teid
LEFT OUTER JOIN dbo.scode scc
ON d.sid = scc.sid
LEFT OUTER JOIN dbo.rcode rc
ON d.rid = rc.rid
LEFT OUTER JOIN dbo.status pac
ON d.pid = pac.pid
END TRY
DECLARE @runDateTime datetime = GETDATE(),
@UserName varchar(100) = system_user
BEGIN CATCH
INSERT INTO [DB2].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
'OrdersFill',
'Insert',
'Attempt Failed')
END CATCH
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
ROLLBACK -- ROLLBACK THE TRANSACTION
END
COMMIT
December 8, 2009 at 1:23 pm
doran_doran (12/8/2009)
Writing my first trigger. Can anyone verify or fix if I am missing anything? Many thanks in advance...CREATE TRIGGER [it_Orders_to_OrdersFill]
ON [Orders]
FOR INSERT
AS
BEGIN TRY
INSERT INTO [DB2].[dbo].[OrdersFill]
(OrderID,
LastName,
FirstName,
MiddleName,
Birthday,
Gender,
Class,
Agency,
Race,
Status)
SELECT d.id,
d.lastn,
d.firstn,
d.mn,
n.DESC,
d.dob,
d.gender,
scc.DESC AS sccDesc,
sac.cyabbrev,
rc.DESC AS rcDesc,
pac.DESCRIPTION AS pacDesc
FROM dbo.Orders d
LEFT OUTER JOIN dbo.fixcode n
ON d.fixid = n.fixid
LEFT OUTER JOIN dbo.cycode ssc
ON d.cyid = ssc.cyid
LEFT OUTER JOIN dbo.tecode sac
ON d.teid = sac.teid
LEFT OUTER JOIN dbo.scode scc
ON d.sid = scc.sid
LEFT OUTER JOIN dbo.rcode rc
ON d.rid = rc.rid
LEFT OUTER JOIN dbo.status pac
ON d.pid = pac.pid
END TRY
DECLARE @runDateTime datetime = GETDATE(),
@UserName varchar(100) = system_user
BEGIN CATCH
INSERT INTO [DB2].[dbo].[logs]
(username,
logdatetime,
tablename,
useraction,
COMMENT)
VALUES (@UserName,
@runDateTime,
'OrdersFill',
'Insert',
'Attempt Failed')
END CATCH
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
ROLLBACK -- ROLLBACK THE TRANSACTION
END
COMMIT
The rollback will also rollback insert to your log table. You should move the rollback into the CATCH block prior to the insert into the log table.
December 8, 2009 at 1:27 pm
1. I moved the roll back statement right after begin catch line but before insert into log
2. Thanks for the quick reply. Do you think I am "FROM INSERTED" from my initial insert statement? I noticed "from inserted or from deleted" etc used in triggers.
3. Also, I get following error when I hit f5
Msg 156, Level 15, State 1, Procedure it_Demographic_to_AREAS, Line 59
Incorrect syntax near the keyword 'DECLARE'.
Msg 139, Level 15, State 1, Procedure it_Demographic_to_AREAS, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure it_Demographic_to_AREAS, Line 73
Must declare the scalar variable "@GetUserName".
December 8, 2009 at 3:28 pm
DECLARE @runDateTime datetime = GETDATE(),
@UserName varchar(100) = system_user
Above is not valid in SQL Server 2005, try:
DECLARE @runDateTime datetime,
@UserName varchar(100);
set @runDate = GETDATE();
set @UserName = system_user;
December 9, 2009 at 10:04 am
I am getting these 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 '+'.
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
December 9, 2009 at 10:12 am
Comment out your subqueries and replace with a static value. See if you still get syntax errors. If not, then there is a problem with one or more of the subqueries.
December 9, 2009 at 10:17 am
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 '+'.
December 9, 2009 at 10:27 am
The second error message is probably caused by this part: EXEC msdb.dbo.sp_send_dbmail . You don't have access to the inserted table at the point you are trying to create and send the email. Comment out that part and check again.
December 9, 2009 at 10:30 am
Lynn,
Actually, at this stage I am just trying to create the trigger so I will not be received access denied.
However, I already check the sp in new windows and able to send mail. Actually, 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 '+'.
December 9, 2009 at 10:35 am
doran_doran (12/9/2009)
Lynn,Actually, at this stage I am just trying to create the trigger so I will not be received access denied.
However, I already check the sp in new windows and able to send mail. Actually, 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 '+'.
You do know that if you double click on the error message it will take you to the part of your code where the error occurred.
December 9, 2009 at 10:39 am
The error on this line....
Incorrect syntax near '+'.
@body = 'A record with id ' + convert(varchar(50), d.id) + ' has been updated.',
December 9, 2009 at 10:40 am
doran_doran (12/9/2009)
The error on this line....Incorrect syntax near '+'.
@body = 'A record with id ' + convert(varchar(50), d.id) + ' has been updated.',
At this point in the trigger you no longer have access to the INSERTED table.
December 9, 2009 at 10:41 am
Concatenating strings as part of a parameter assignment to a stored proc is not allowed. Define a variable of type varchar, build up the string and set the parameter value to that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2009 at 10:43 am
@body = 'A record with id ' + convert(varchar(50), d.id) + ' has been updated.',
I am getting this error
Incorrect syntax near '+'.
December 9, 2009 at 10:46 am
As I just said:
Concatenating strings as part of a parameter assignment to a stored proc is not allowed. Define a variable of type varchar, build up the string and set the parameter value to that.
Plus what exactly is d.id supposed to refer to there? It's not a select statement, there's no FROM defining a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply