Help with 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 2005 Forums

Transact-SQL (2005)

Help with TRIGGER

Author

Topic

dariopalermo
Starting Member

16 Posts

Posted - 2009-07-15 : 05:43:38

I've got 2 DB and i want to write in the second DB some data using a trigger on the first DB

USE [FleetSrv]
GO
/****** Object: Trigger [dbo].[EventiCSTP] Script Date: 07/15/2009 11:22:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[EventiCSTP]
ON [dbo].[Storici]
FOR INSERT
AS
INSERT CSTP.dbo.TESTTABLE (Data, Ora, Lat, Lon)
SELECT sto_data,
sto_ora,
CAST(sto_latOrig AS float) / 600,
CAST(sto_lonOrig AS float) / 600
FROM inserted
WHERE sto_allarm ='237'

When i create this trigger, the table "Storici" will stop updating, and no data will be ever written to CSTP.dbo.TESTTABLE

Anyone can help?

Thanks

Dario

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 05:56:29

any error message?
have permissions on CSTP.dbo.TESTTABLE ?

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-15 : 06:22:40

change trigger type to after insert, in this case if our trigger fails value is still inserted

dariopalermo
Starting Member

16 Posts

Posted - 2009-07-15 : 06:28:36

russel, your reply was the One ;)
The application that inserts records in the first db is working with a sql user that wasn't able to write into the second DB. Now i've fixed it and it works :)

asgast, I'll change the trigger to after insert, it seems better just as you suggested.

Thanks guys :)

Bye, Dario

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-15 : 06:53:21

quote:
Originally posted by asgast

change trigger type to after insert, in this case if our trigger fails value is still inserted

if that's the behavior you want. if you need to ensure both records are written or none, don't change it

dariopalermo
Starting Member

16 Posts

Posted - 2009-07-15 : 07:35:54

the app that's using the first db is far more important, so yes, i want to! :)

now i have another question...

This is the actual working trigger (had to make some other mods to it):

USE [FleetSrv]
GO
/****** Object: Trigger [dbo].[EventiCSTP] Script Date: 07/15/2009 12:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[EventiCSTP]
ON [dbo].[Storici]
FOR INSERT
AS
INSERT CSTP.dbo.TESTTABLE
SELECT sto_data AS Data,
sto_ora AS Ora,
dbo.Utenza.ute_codeV AS IDveicolo,
CAST(sto_latOrig AS float) / 600 AS Lat,
CAST(sto_lonOrig AS float) / 600 AS Lon,
'notavail'
FROM inserted INNER JOIN
dbo.Utenza ON sto_utenza = dbo.Utenza.ute_id INNER JOIN
dbo.Veicoli ON dbo.Utenza.ute_codeV = dbo.Veicoli.ana_codeV
WHERE sto_allarm ='237' AND dbo.Veicoli.ana_flotta = '1'

Now, where you find 'notavail', i want to put the last Storici.sto_msg value from dbo.Storici whith storici.sto_utenza = inserted.sto_utenza, so it's an inner join with dbo.storici, but i need only the last record matching from that table...

i'll explain what the db is:

The db is a list of events received from multiple bus fleets.

event 237 is for: Start stop

i must put in a different db (CSTP) all the stops that the busses of (fleet with code '1') are doing, with also the current "travel path" the bus is doing. I can extract the travel path also from Storici table, filtered by event 239. To make it simple:

Event Event_message Date/time IDvehicle
239 path n. 01 xxx yyy 001 (the driver has put the travel code in the onboard computer)
237 stop start xxx yyy2 001 (first bus stop)

Hope it's clear enough

Bye, Dario

dariopalermo
Starting Member

16 Posts

Posted - 2009-07-15 : 07:37:00

ops, it's not for insert anymore, now it's after insert :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources