trigger in SQLserver - 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

trigger in SQLserver

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 12:22:24

Hi friends,

We have triggers on each table in oracle database and none in sqlserver so far. But we are looking at creating triggers on sqlserver db's as well and I am trying to find the equivalent syntax for T sql..

In ORACLE, for edi_invoice column on EDI table:

CREATE OR REPLACE TRIGGER edi_trg
before insert or update ON edi for each row
begin

:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
end
/

I think in T SQL is should be like below?

CREATE TRIGGER edi_trg
ON edi
for insert or update
as

:new.EDI_INVOICE:= nvl(:new.EDI_INVOICE,' ');
go

Thanks much for all your help.

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-06 : 12:30:19

Do you have sql server management studio?

Do you know what books online (BOL) is?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 12:59:41

Yes I have SQL Mgmt studio.

Thanks, Brett.

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-06 : 13:10:57

Well then open Books online and then go to the Index and look up CREATE TRIGGER

UPDATE E
SET EDI_INVOICE = COALESCE(i.EDI_INVOICE,' ')
FROM EDI E INNER JOIN inserted i
ON E.key = i.key

But honestly, the DML operation will do that for you, so I don't now what you want.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 13:29:55

Brett,

There is about 15 columns in that table, so would like to go with the trigger, also since there is a trigger on oracle on the same table. Its a development environment,so there is both oracle and sqlserver databases with the same structure except but the triggers.

So, I am trying to find the syntax equivalent to the oracle SQL trigger syntax.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 13:36:45

psangeetha,

You haven't told us what the trigger needs to do. Most of us do not use Oracle, so we are unable to figure out what your Oracle trigger is doing.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-06 : 13:43:46

new is the inserted table

And there is no need to reference it in a join like I should

And, they have to specify for each row, it is not set based

It's very much like DB2

BUT

Your Oracle trigger doesn't make much sense, since the DML operation will just do the update

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 14:05:22

Brett/Tara Kizer,

Thanks for your support.

I think the trigger was created to substitute a value(column value), if it was entered null. The databases have been created like few years back and I'm able to predict only by looking at the code..

Is there a way to create trigger in SQLserver the same way for all the columns?

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 14:07:25

If you don't understand the code, then I don't see how we can help.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-06 : 14:24:10

why not just change the procedure? Or is the DB access by sql from the front end?

In any case




CREATE TRIGGER dbo_MEP_TR 
	    ON dbo.MEP 
	   FOR INSERT
SET NOCOUNT ON 

   UPDATE <table>
      SET Col1 = COALESCE(COL1,' ')
	, Col2 = COALESCE(COL2,' ')
	WHERE Col1 IS NULL
	   OR Col2 IS NULL

But that's REALLY bad form

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 14:28:32

Tara,

Here is the situation, its okay if I do this different in oracle and sqlserver databases. But this is what I have to do, add column billdate(datatype datetime) to EDI table. The date and time must not change when a record is updated, it should change when a new record is being inserted.

For this task, I am going to have two triggers in oracle, first trigger(for insert) with column billdate and second trigger(for update) without column billdate.

Is it possible to have this done on sqlserver as well?

Thank you so much.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-05-06 : 15:02:47

Brett,

I'm guessing that I could the same code for this task as well..

CREATE TRIGGER edi.trg
ON edi
FOR INSERT
SET NOCOUNT ON

INSERT edi
SET billdate = COALESCE(billdate,' ')
WHERE billdate IS NULL

THank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources