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 2000 Forums

Transact-SQL (2000)

Trigger

Author

Topic

puja
Starting Member

18 Posts

Posted - 2004-04-12 : 15:33:57

Hi,
I have question on Trigger. I am new to Trigger. I wrote my Trigger for my current table and it does update my rows;however, when
i try to INSERT the multiple rows at same time then my Trigger does not invoke.

can someone help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 15:40:05

Did you create the trigger for UPDATE and INSERT? It has to be specified in the trigger code what kind it is. Post your trigger code here if you need more help.

Tara

puja
Starting Member

18 Posts

Posted - 2004-04-12 : 16:18:34

Hi,
I create the Trigger for INSERT

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 16:24:08

My damn telepathy is on the fritz again...

maybe if you post the trigger...

Wait...I'm gettin a picture

SELECT @Col1 = Col1 FROM inserted

Update Table2 Set Coo1 = @Col1

Close?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:33:58

If the trigger is for INSERT only, then it will not work for UPDATE, only INSERTs. You must post the code in order for us to help.

Tara

puja
Starting Member

18 Posts

Posted - 2004-04-12 : 16:42:02

ok, This is what i have written for my trigger to fire up every time there is instance new record gets inserted.

My Trigger will replace my col1 adding a one character reading it from my col2.
Create Trigger Update_column
On emp_trigger
FOR INSERT
as
Declare @i char(2)
declare @name char (11)

set @name = select firstname from inserted p, emp_trigger e where e.id = p.id
IF (@i = 'tom')
Begin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:45:38

There must be more to your code as that won't even compile.

Just so that you know, the inserted table can contain more than one row, so you can not put the data into a variable as variables can only contain one item at a time. Your set @name part is what I am referring to. @name will only contain the last firstname in the inserted table. But inserted could have more than one row.

Tara

puja
Starting Member

18 Posts

Posted - 2004-04-12 : 16:45:48

set @name = select col2 + 'M' from inserted i, emp_trigger where e.id = i.id
Update emp_trigger
set col2 = @name
from emp_trigger e , inserted i where i.id = e.id and i.col2 like '%Johnson%'
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:49:27

Where does @i get set? It isn't being set in your trigger. Please explain what you are trying to do. Explain by showing data, what the data initially looks like and what it is supposed to look like afte the trigger fires.

Tara

puja
Starting Member

18 Posts

Posted - 2004-04-12 : 16:50:58

Do i have to create a loop will check if the more than one row is being inserted or not?

If it's more than one then i write some kind of check that will do the checking and update the column.

can you please help me what would be best way to do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:54:47

If you posted some data (before and after the trigger fires), we could help you write the code. Looping is never ideal in SQL. You can use joins with updates and inserts. This is probably what would be used here.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 16:59:20

Hey I guessed right.....

You have to remember that we live in a set based world...

So the assignment of Many rows to a variable will set the variable to the last one of a set..

And are you telling us thea emp_trigger is a table? Funny name (and confusing) name for a table....

You need to do an set update...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:28:03

Please do not post the information in e-mail as others will not be able to see it. Here is what puja e-mailed me:

Here is my table which i am calling name: emp
INSERT INTO emp(mname,sname,id,rescode)
values('VAADAA', 'TEST001', '1','12')
INSERT INTO emp(mname,sname,id, rescode)
values('VAADAB', TEST002', 4, '23')

I have written the trigger on my emp table which will search entire the sname colm and if it found 1, it will then update the mname colm with ending 1 at the end. and it founds the "2" my mname colum will end with "2" at the end.
That is the logic i am trying to do on my trigger.
I just only want to change all of my mname cloum where like 'VAADAA' and VAAPDA' other rows should not be updated.
hope this helps

Here is my take on it:



CREATE TRIGGER itrg_emp
ON emp
AFTER INSERT
AS

UPDATE e
SET e.mname = i.mname + '1'
FROM emp e
INNER JOIN inserted i
ON i.mname = e.mname
WHERE i.mname IN ('VAADAA', 'VAAPDA') AND PATINDEX('%1%', i.sname) > 0

UPDATE e
SET e.mname = i.mname + '2'
FROM emp e
INNER JOIN inserted i
ON i.mname = e.mname
WHERE i.mname IN ('VAADAA', 'VAAPDA') AND PATINDEX('%2%', i.sname) > 0

GO

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources