trigger that will update one table when a record is updated in another

Question

Post reply

trigger that will update one table when a record is updated in another

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
January 14, 2013 at 10:29 am

#272740

I'm trying to write a trigger that will update one table [tblTransactions] when a record is updated in another [tblBulkPurchases].... am I on the right track here?
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trUpdateBulkPurchases]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
UPDATE tblBulkPurchases
(tblBulkPurchases.PO_Number,
tblBulkPurchases.Quantity,
tblBulkPurchases.Transaction_Number,
tblBulkPurchases.Quantity_Remaining,
tblBulkPurchases.Unit_Price,
tblBulkPurchases.Software_Description,
tblBulkPurchases.PO_Date,
tblBulkPurchases.PurchaseCostCenter,
tblBulkPurchases.HeatTicketNumber,
tblBulkPurchases.PurchaseAccount,
tblBulkPurchases.Transaction_Date,
tblBulkPurchases.Transaction_Type,
tblBulkPurchases.SoftwareShortName)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Number,
INSERTED.Quantity,
INSERTED.Unit_Price,
INSERTED.Software_Description,
INSERTED.PO_Date,
INSERTED.PurchaseCostCenter,
INSERTED.HeatTicketNumber,
INSERTED.PurchaseAccount,
INSERTED.Transaction_Date,
INSERTED.Transaction_Type,
INSERTED.SoftwareShortName
FROM INSERTED
WHERE Transaction_Type = 'Bulk Purchase'
END
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 1

doesn't look quite right to me.

It looks to me like you are missing a relationship between t he two tables.

maybe the PO_Number exists between the two table,s and you need to update WHERE PO_NUMBER = INSERTED.PO_Number AND Transaction_Type = 'Bulk Purchase' ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

Correct... do need to associate by PO = PO... thanks.... is the word INSERTED.____ appropriate to use?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

INSERTED.___ is fine.

I'd do this, but your way works:

UPDATE tblBulkPurchases

(tblBulkPurchases.PO_Number,

tblBulkPurchases.Quantity,

tblBulkPurchases.Transaction_Number,

tblBulkPurchases.Quantity_Remaining,

tblBulkPurchases.Unit_Price,

tblBulkPurchases.Software_Description,

tblBulkPurchases.PO_Date,

tblBulkPurchases.PurchaseCostCenter,

tblBulkPurchases.HeatTicketNumber,

tblBulkPurchases.PurchaseAccount,

tblBulkPurchases.Transaction_Date,

tblBulkPurchases.Transaction_Type,

tblBulkPurchases.SoftwareShortName)

SELECT

i.PO_Number,

i.Quantity,

i.Transaction_Number,

i.Quantity,

...

FROM INSERTED i

inner join tblBulkPurchases

on tblBulkPurchases.PO = i.po

WHERE Transaction_Type = 'Bulk Purchase'

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

Kinda like your way... but the compiler doesn't like where I've placed astericks:

USE [TrackIT]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trUpdateBulkPurchases]

ON [dbo].[tblTransactions]

AFTER UPDATE

AS

BEGIN

UPDATE tblBulkPurchases

*(tblBulkPurchases.PO_Number,

tblBulkPurchases.Quantity,

tblBulkPurchases.Transaction_Number,

tblBulkPurchases.Quantity_Remaining,

tblBulkPurchases.Unit_Price,

tblBulkPurchases.Software_Description,

tblBulkPurchases.PO_Date,

tblBulkPurchases.PurchaseCostCenter,

tblBulkPurchases.HeatTicketNumber,

tblBulkPurchases.PurchaseAccount,

tblBulkPurchases.Transaction_Date,

tblBulkPurchases.Transaction_Type,

tblBulkPurchases.SoftwareShortName)

SELECT

i.PO_Number,

i.Quantity,

i.Transaction_Number,

i.Quantity,

i.Unit_Price,

i.Software_Description,

i.PO_Date,

i.PurchaseCostCenter,

i.HeatTicketNumber,

i.PurchaseAccount,

i.Transaction_Date,

i.Transaction_Type,

i.SoftwareShortName

FROM INSERTED i

inner join tblBulkPurchases

on tblBulkPurchases.Transaction_Number = i.Transaction_Number

WHERE Transaction_Type = 'Bulk Purchase' *

GO

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Sorry, wasn't reading well. The problem is that you have a malformed UPDATE statement.

CREATE TRIGGER [dbo].[trUpdateBulkPurchases]

ON [dbo].[tblTransactions]

AFTER UPDATE

AS

BEGIN

UPDATE tblBulkPurchases

set PO_Number = i.PO_Number

, Quantity = i.quantity

...

FROM INSERTED i

inner join tblBulkPurchases

on tblBulkPurchases.Transaction_Number = i.Transaction_Number

WHERE Transaction_Type = 'Bulk Purchase'

GO

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 6

Like this? (Still doesn't like near where the * is):

USE [TrackIT]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trUpdateBulkPurchases]

ON [dbo].[tblTransactions]

AFTER UPDATE

AS

BEGIN

UPDATE tblBulkPurchases

SET

PO_Number = i.PO_Number,

Quantity = i.Quantity,

Unit_Price = i.Unit_Price,

Software_Description = i.Software_Description,

PO_Date = i.PO_Date,

PurchaseCostCenter = i.PurchaseCostCenter,

HeatTicketNumber = i.HeatTicketNumber,

PurchaseAccount = i.PurchaseAccount,

Transaction_Date = i.Transaction_Date,

SoftwareShortName = i.SoftwareShortName

FROM INSERTED i

inner join tblBulkPurchases

on tblBulkPurchases.Transaction_Number = i.Transaction_Number

WHERE Transaction_Type = 'Bulk Purchase' *

GO

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

What's the error? If it's an ambiguous column, you need to specify from which table (The base one or INSERTED) you are specifying in the WHERE clause.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 8

WHERE Transaction_Type = 'Bulk Purchase'

Msg 102, Level 15, State 1, Procedure trUpdateBulkPurchases, Line 21

Incorrect syntax near 'Bulk Purchase'.

toddasd SSCertifiable Points: 6298 More actions · Answer 9

briancampbellmcad (1/14/2013)
Like this? (Still doesn't like near where the * is):
...
WHERE Transaction_Type = 'Bulk Purchase' *
GO

Yes, like that but no asterix. You won't be using an * here.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 10

Do you have an asterisk in your code? I thought you were using the to mark the place you were writing about.

No asterisks needed in your code.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 11

Correct... the asterick was just to show in the post where the problem was. There's no asterick in the code.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

you may need to qualify as I mentioned:

USE [TrackIT]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trUpdateBulkPurchases]

ON [dbo].[tblTransactions]

AFTER UPDATE

AS

BEGIN

UPDATE tblBulkPurchases

SET

PO_Number = i.PO_Number,

Quantity = i.Quantity,

Unit_Price = i.Unit_Price,

Software_Description = i.Software_Description,

PO_Date = i.PO_Date,

PurchaseCostCenter = i.PurchaseCostCenter,

HeatTicketNumber = i.HeatTicketNumber,

PurchaseAccount = i.PurchaseAccount,

Transaction_Date = i.Transaction_Date,

SoftwareShortName = i.SoftwareShortName

FROM INSERTED i

inner join tblBulkPurchases

on tblBulkPurchases.Transaction_Number = i.Transaction_Number

WHERE tblBulkPurchases.Transaction_Type = 'Bulk Purchase'

mister.magoo SSC-Forever Points: 47068 More actions · Answer 13

I think it quite likely that it doesn't like you using BEGIN without an END...:-P

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 14

DaveBriCam

SSCarpal Tunnel

Points: 4294

January 15, 2013 at 8:05 am

#1577353

Thank you... all is working!