TRIGGER with PARAMETER - 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

SQL Server Development (2000)

TRIGGER with PARAMETER

Author

Topic

kamalkishore_in
Yak Posting Veteran

76 Posts

Posted - 2002-10-13 : 05:23:10

Hello,

While UPDATING the itemName field of TABLE_A for a perticlar itemCode, I have to check TABLE_B as if it contains the same itemCode as of TABLE_A, I have to change the itemName field of TABLE_B with the itemName of TABLE_A.

What are the options availbel here, as I have Around 10,000 Records in TABLE_A and 2,00,000 Records in Table_B.

Is it possible to pass PARAMERTER to Trigger, if yes HOW?

Help me...

Thankx and waiting

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-13 : 15:13:06

The trigger already has access to information that you inserted, updated, or deleted.

The old information (deleted or updated is contained in
table del

The new information (inserted or updated is contained in
table ins

/****************************************************************/
CREATE TRIGGER updTABLE_A ON TABLE_A
FOR update AS

/*Check TABLE_B if it contains the same itemCode as of TABLE_A change itemName field of TABLE_B with the itemName of TABLE_A.*/

DECLARE @itemCodeA AS Integer
DECLARE @itemNameA As NVARCHAR(255)

SELECT @itemCodeA = ins.itemCode, @itemNameA = ins.ItemName A FROM inserted ins

UPDATE TABLE_B
SET itemName = @itemNameA
WHERE itemCode = @itemCodeA

/*******************************************************************/

You can also update from a join see this article for an example
http://sqlmag.com/Articles/Index.cfm?ArticleID=8808

Warning: Code above was not tested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources