Need help with triggers URGENT!!!

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

Need help with triggers URGENT!!!

Author

Topic

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 02:50:50

Hi,
I am trying write a trigger for,whenever a row is inserted in the base table it has to inserted into the audit table also.Am facing problems with the syntax of the trigger could anyone pls help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-12 : 03:17:02

What have you written this far?

N 56°04'39.26"
E 12°55'05.63"

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 04:00:05

Have tried so many...
Receent one was
create trigger triggername on tablename
for insert
as

declare @var1;
declare @var2;

BEGIN
set @var1 = select var1 from inserted;
set @var2 = select var2 from inserted;

insert into tablename_audit
(var1,var2)
values
(@var1,@var)

END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-12 : 04:43:06

[code]CREATE TRIGGER dbo.trgTriggerName
ON dbo.TableName
AFTER INSERT
AS

SET NOCOUNT ON

INSERT dbo.TableName_Audit
(
Var1,
Var2
)
SELECT Var1,
Var2
FROM inserted;[/code]

N 56°04'39.26"
E 12°55'05.63"

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 05:19:50

Hi SwePeso,
Thanks for reply.but still I face some syntax error near the select statement.I have worked on oracle triggers,
but totally new to sql server triggers so facing syntax issues :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-12 : 05:20:41

What is the syntax error you get?

N 56°04'39.26"
E 12°55'05.63"

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 05:26:58

Error near select statement ','

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 05:45:46

CREATE TRIGGER dbo.trgTriggerName
ON dbo.TableName
AFTER INSERT
AS

SET NOCOUNT ON

Declare @var1 varchar(50)
Declare @var2 varchar(50)

select @var1= var1 from inserted
select @var2= var2 from inserted
INSERT into dbo.TableName_Audit
(
Var1,
Var2
)values
(@var1,@var)
end

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 06:30:34

HI Vijays3,
Thanks for the reply,still am getting error,
it says "incorrect syntax near 'inserted'"

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 06:52:05

quote:
Originally posted by Maheswari

HI Vijays3,
Thanks for the reply,still am getting error,
it says "incorrect syntax near 'inserted'"

 Sorry my mistake .Check this out


CREATE TRIGGER	dbo.trgTriggerName
ON	 dbo.TableName
AFTER	 INSERT 
AS

SET NOCOUNT ON

Declare @var1 varchar(50)
Declare @var2 varchar(50)

select @var1= i.var1 from inserted i
select @var2= i.var2 from inserted i
INSERT	into dbo.TableName_Audit
(
Var1,
Var2
)values
(@var1,@var2)
end

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-12 : 07:03:22

You can insert more than one row into a table using a single insert statement. When you do, even though more than one row is inserted, the trigger is called only once. Code Peso posted will work correctly in such cases. Even if you think your current code and logic is set to insert only one row at a time, sooner or later you will run into this problem. And, that will take enormous amounts of efforts to debug and fix. Sadly, I am speaking from painful experience.

So use this code, which is exactly same as Peso's except for some case changes. I am suspecting that you have a case sensitive collation. If you do make sure that you use the correct/consistent upper/lower case everywhere

CREATE TRIGGER dbo.trgTriggerName
ON dbo.TableName
   AFTER INSERT
AS
	SET NOCOUNT ON
	
	INSERT dbo.TableName_Audit
	  (
	    Var1,
	    Var2
	  )
	SELECT Var1,
	       Var2
	FROM   INSERTED;

You can find what your collation is using this:

SELECT SERVERPROPERTY('collation')

Maheswari
Starting Member

6 Posts

Posted - 2012-05-12 : 07:29:26

HI Sunitabeck ,
Thanks but when i use this code i get a syntax error near the select statement,any idea y?

And thanks Vijay this code seems to be working fine as of now.
hopefully there should not be a problem while inserting more number of rows,if so I ll get back here again :)

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 07:43:26

Welcome.Anytime...

Vijay is here to learn something from you guys.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-13 : 01:19:30

Sigh...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources