Need Help While Creating Trigger

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

SQL Server Administration (2005)

Need Help While Creating Trigger

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-17 : 04:50:25

Hello,

I do have one table "ABC" in DB "Master" & other table "XYZ" in DB "Test".

Both tables are having same structures & same data currently. Now i want to create trigger in such a way that after every insertion & updation on table "ABC" in DB "Master" will insert & update records in table "XYZ" in DB "Test" respectively.

Can any one help me out?

Thanks
Prashant Hirani
Where Dreams Never Ends

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-17 : 05:55:15

Have a look at this thread, quite similar problem with similar solution -> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101147

--
Lumbago

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-17 : 07:14:49

in my both tables
(Expense_Code, Expense_Version) is mine composite primary key.

i have create following trigger. it works fine in the case of insertion.

At the time of updation [except fields "Expense_Code, Expense_Version"], it's trying to insert whole new row with the same ["Expense_Code, Expense_Version"] and so it gives me error "Violation of PRIMARY KEY constraint".

i need to update below trigger in such a way that, at the time of updation, it will only update the respective row, means it should perform updation only....

CREATE TRIGGER TR_EXPENSE_MST
ON expense_mst
AFTER INSERT, UPDATE
AS
BEGIN
INSERT INTO TRADECAPTURE_NEW..EXPENSE_MST
(Expense_Code, Expense_Version, Expense_Desc, Authorize_Emp,
Max_Limit, amex_show,
)
SELECT P.Expense_Code, P.EXPENSE_VERSION, P.Expense_Desc,
P.Authorize_Emp, P.Max_Limit, P.amex_show

FROM expense_mst P INNER JOIN INSERTED I ON
P.Expense_Code = I.Expense_Code

Can any experts help me out?

Thanks
Prashant

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-17 : 08:44:44

You can use
CURSORS <@CURSOR-PRIMARYKEY> to store the Primary Keys of the Inserted records and check
IF(EXISTS(SELECT <@CURSOR-PRIMARYKEY> FROM INSERTED> )) and then Update or Insert accordingly.

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-17 : 09:17:52

Thanks coagulance,

i am not getting you.

can you explain me with some example, if possible?

Thanks
Prashant Hirani
Where Dreams Never Ends

svicky9
Posting Yak Master

232 Posts

Posted - 2008-04-17 : 09:51:24

There are many ways to do this

One way would be.

place a validation check before the insert command to check whether the 2 column already exists in the table if exists you should do an update statement.

My Trigger may be like this

CREATE TRIGGER TR_EXPENSE_MST
ON expense_mst
AFTER INSERT, UPDATE
AS
BEGIN

declare @iex_code varchar(10)
declare @iex_ver varchar(10)

select @iex_code = I.Expense_Code,@iex_ver = I.Expense_Version from inserted I

if exists(select * from TRADECAPTURE_NEW..EXPENSE_MST where Expense_Code = @iex_code and Expense_version = @iex_ver)

then

Begin
--Write the Appropriate statement

End

INSERT INTO TRADECAPTURE_NEW..EXPENSE_MST
(Expense_Code, Expense_Version, Expense_Desc, Authorize_Emp,
Max_Limit, amex_show,
)
SELECT P.Expense_Code, P.EXPENSE_VERSION, P.Expense_Desc,
P.Authorize_Emp, P.Max_Limit, P.amex_show

FROM expense_mst P INNER JOIN INSERTED I ON
P.Expense_Code = I.Expense_Code

Vic

http://vicdba.blogspot.com

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-04-18 : 07:16:33

Hey, it's working man. Gr8

Thanks you very much.

Prashant Hirani

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-18 : 07:34:10

This trigger will break if there are more then one row updated/inserted. Your select will only fetch the last expense_code/version that is updated and your trigger will fail if you update rows with different expense_code/versions and they exist in the target table.

Exact same problem in the link I posted earlier.

--
Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources