Create Trigger Help - 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 2005 Forums

SQL Server Administration (2005)

Create Trigger Help

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-09 : 02:17:10

i do have my 'Product' TABLE IN DATABASE 'ABC'

Product TABLE OUTPUT

PRODUCT_CODE PRODUCT_TYPE PRODUCT_DESC PRODUCT_ID PRODUCT_GROUP_CODE
6001 computer NULL ENVD 14
6002 keyboard NULL ENVD 14
6003 mouse NULL ENVD 14
6004 cables NULL ENVD 14
6005 processor NULL ENVD 14

AND 'Product_Mst' TABLE IN DATABASE 'XYZ'

Product_Mst OUTPUT

PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14

Now i want TO CREATE TRIGGER such that every updation in Product TABLE will UPDATE the appropriate record IN Product_Mst

FOR example IF i fire below query IN ABC Database

UPDATE Product
SET PRODUCT_DESC = 'Available' WHERE Product_Code = 6001

Then the OUTPUT OF the Product_Mst shoub be..

Product_Mst OUTPUT

PROD_CODE Prod_Ver PROD_TYPE PROD_DESC PROD_ID PROD_GRP_CODE
6001 0 computer NULL ENVD 14
6001 1 computer NULL ENVD 14
6002 0 keyboard NULL ENVD 14
6003 0 mouse NULL ENVD 14
6004 0 cables NULL ENVD 14
6005 0 processor NULL ENVD 14

Means i want to increment the version by 1 and Insert that records into Product_Mst Table at every updation.

I hope i am clear with my question.

Regards
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 09:42:40

[code]CREATE TRIGGER MyTrigger ON ABC..Product
AFTER UPDATE
AS
INSERT INTO XYZ..Product_Mst
SELECT pm.PROD_CODE,
pm.Prod_Ver+1,
pm.PROD_TYPE,
pm.PROD_DESC,
pm.PROD_ID,
pm.PROD_GRP_CODE
FROM XYZ..Product_Mst pm
INNER JOIN INSERTED i
ON i.PRODUCT_CODE=pm.PRODUCT_CODE
GO[/code]

and if PRODUCT_CODE is an identity column, you need enable identity insert on Product_Mst table using
SET IDENTITY_INSERT XYZ..Product_Mst ON
and set it to off after insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources