Newsgroup: it.comp.software.database
Titolo: SQLSERVER2K: scrivere su una tabella con transazione ROLLBACKKATA..

Ciao a tutti.
All'interno di un TRIGGER BEFORE INSERT faccio delle verifiche sui dati, se
qualcosa non va alzo un errore ed eseguo il ROLLBACK TRANSACTION per
impedire la scrittura del record.
Però vorrei riuscire a scrivere su una tabella di LOG un messaggio ma,
eseguendo il ROLLBACK, credo esegua anche il rollback della scrittura di
tale messaggio di log.
E' possibile aggirare il problema ?
Ciao e grazie.
Roby
Codice SQL....
ALTER       TRIGGER [LOGMOVMA_BEFORE_INSERT] ON dbo.ES_LOGMOVMA
FOR INSERT NOT FOR REPLICATION
<... bla bla ... >
 IF @ERRVAR <> 0  -- Errore rilevato dal codice precedente...
 BEGIN
  PRINT @MSGERR    -- @MSGERR contiene la descriz. dell'errore...
  RAISERROR (@MSGERR, 16, 1)
  ROLLBACK TRANSACTION
  EXEC SP100LOG_WRITEMESSAGE    -- Scrittura del msg. di log in un'altra
tabella...
   @CODSHOP=@LASTCODSHOP,
   @LEVEL=1,
   @AREA='MAG',
   @MESSAGE='Errori prima dell''inserimento nel log del magazzino.'

FOR INSERT NOT FOR REPLICATION
non e' un trigger di tipo BEFORE (SQL Server non li supporta),
ma un trigger di tipo AFTER (scatta ad aggiornamento avvenuto),
SQL Server supporta pero' i trigger di tipo INSTEAD OF ed e'
una delle soluzioni proposte di seguito.
--<CUT HERE>--
USE tempdb
GO
IF OBJECT_ID('dbo.NewAuthors') IS NOT NULL
 DROP TABLE dbo.NewAuthors
IF OBJECT_ID('dbo.AuditAuthors') IS NOT NULL
 DROP TABLE dbo.AuditAuthors
/*
** creazione copia tabella authors
*/
SELECT *
INTO dbo.NewAuthors
FROM pubs.dbo.authors
/*
** creazione tabella di audit vuota
*/
SELECT CAST('' as varchar(100)) AS errore, *
INTO dbo.AuditAuthors
FROM pubs.dbo.authors
WHERE 1=2 -- non si verifica mai
GO
/*
** soluzione 1: usare un trigger di tipo instead of
*/
CREATE TRIGGER NewAuthors_tgi
ON dbo.NewAuthors
INSTEAD OF INSERT
AS
IF EXISTS( SELECT *
   FROM inserted
   WHERE phone = ''
  )
BEGIN
 INSERT dbo.AuditAuthors
 SELECT 'telefono non specificato', *
 FROM inserted
END
ELSE
BEGIN
 -- nei trigger di tipo instead of devi
 -- proccuparti di fare veramente l'operazione
 INSERT dbo.NewAuthors
 SELECT *
 FROM inserted
END
GO
-- riga errata ...
INSERT dbo.NewAuthors
VALUES ('999-99-9999', 'Paolino', 'Paperino', '',
  'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0)
-- ... viene tracciata
SELECT * FROM AuditAuthors
-- riga corretta
INSERT dbo.NewAuthors
VALUES ('999-99-9999', 'Paolino', 'Paperino', '113 113-1313',
   'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0)
SELECT * FROM NewAuthors WHERE au_id = '999-99-9999'
-- Pulizia tabella
DELETE NewAuthors WHERE au_id = '999-99-9999'
DELETE AuditAuthors
GO
/*
** soluzione 2: usare una stored procedure e ridondare
** i controlli (potresti lasciarli anche solo nella
** stored procedure, non ti proteggeresti pero'
** da eventuali inserimento effettuati direttamente)
*/
ALTER TRIGGER NewAuthors_tgi
ON dbo.NewAuthors
FOR INSERT
AS
IF EXISTS( SELECT *
   FROM inserted
   WHERE phone = ''
  )
BEGIN
  ROLLBACK TRANSACTION
END
GO
IF OBJECT_ID('dbo.spInsertAuthor') IS NOT NULL
 DROP PROCEDURE dbo.spInsertAuthor
GO
CREATE PROCEDURE dbo.spInsertAuthor
 @au_id  varchar(11),
 @au_lname varchar(40),
 @au_fname varchar(20),
 @phone  char(12),
 @address varchar(40),
 @city  varchar(20),
 @state  char(2),
 @zip  char(5),
 @contract bit
AS
IF @phone = ''
BEGIN
 INSERT dbo.AuditAuthors
 VALUES ('Telefono non specificato',
    @au_id, @au_lname, @au_fname, @phone, @address,
    @city, @state, @zip, @contract)
END
ELSE
BEGIN
 INSERT dbo.NewAuthors
 VALUES (@au_id, @au_lname, @au_fname, @phone, @address,
    @city, @state, @zip, @contract)
END
GO
-- riga errata ...
EXEC dbo.spInsertAuthor '999-99-9999', 'Paolino', 'Paperino', '',
   'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0
-- ... viene tracciata
SELECT * FROM AuditAuthors
-- riga corretta
INSERT dbo.NewAuthors
VALUES ('999-99-9999', 'Paolino', 'Paperino', '113 113-1313',
   'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0)
SELECT * FROM NewAuthors WHERE au_id = '999-99-9999'
-- Pulizia tabella
DELETE NewAuthors WHERE au_id = '999-99-9999'
DELETE AuditAuthors
GO
/*
** soluzione 3: usare una stored procedure che
** controlla il rollback usando una tabella temporanea
** per farsi passare i dati errati dal trigger e una
** variabile di tipo tabella per salvare le righe
** poco pratica, inoltre lascia la gestione alla
** procedura chiamante
*/
ALTER TRIGGER NewAuthors_tgi
ON dbo.NewAuthors
FOR INSERT
AS
IF EXISTS( SELECT *
   FROM inserted
   WHERE phone = ''
  )
BEGIN
 -- verifica esistenza tabella temporanea
 IF OBJECT_ID('tempdb..#AuditAuthors') IS NULL
  ROLLBACK TRANSACTION
 ELSE
  INSERT #AuditAuthors
  SELECT 'telefono non specificato', *
  FROM inserted
END
GO
IF OBJECT_ID('dbo.spInsertAuthor') IS NOT NULL
 DROP PROCEDURE dbo.spInsertAuthor
GO
CREATE PROCEDURE dbo.spInsertAuthor
 @au_id  varchar(11),
 @au_lname varchar(40),
 @au_fname varchar(20),
 @phone  char(12),
 @address varchar(40),
 @city  varchar(20),
 @state  char(2),
 @zip  char(5),
 @contract bit
AS
CREATE TABLE #AuditAuthors(
 error  varchar(100)NULL,
 au_id  varchar(11) NULL,
 au_lname varchar(40) NULL,
 au_fname varchar(20) NULL,
 phone  char(12) NULL,
 address  varchar(40) NULL,
 city  varchar(20) NULL,
 state  char(2) NULL,
 zip   char(5) NULL,
 contract bit NULL
)
DECLARE @AuditAuthors TABLE(
 error  varchar(100)NULL,
 au_id  varchar(11) NULL,
 au_lname varchar(40) NULL,
 au_fname varchar(20) NULL,
 phone  char(12) NULL,
 address  varchar(40) NULL,
 city  varchar(20) NULL,
 state  char(2) NULL,
 zip   char(5) NULL,
 contract bit NULL
)
BEGIN TRANSACTION
INSERT dbo.NewAuthors
VALUES (@au_id, @au_lname, @au_fname, @phone, @address,
   @city, @state, @zip, @contract)
IF @@ERROR | (SELECT COUNT(*) FROM #AuditAuthors) > 0
BEGIN
 -- la variabile tabella sopravvive al rollback
 INSERT @AuditAuthors
 SELECT *
 FROM #AuditAuthors
 ROLLBACK TRANSACTION
 INSERT AuditAuthors
 SELECT *
 FROM @AuditAuthors
END
GO
-- riga errata ...
EXEC dbo.spInsertAuthor '999-99-9999', 'Paolino', 'Paperino', '',
   'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0
-- ... viene tracciata
SELECT * FROM AuditAuthors
-- riga corretta
INSERT dbo.NewAuthors
VALUES ('999-99-9999', 'Paolino', 'Paperino', '113 113-1313',
   'Via Dei Glicini, 13', 'Paperopoli', 'PA', '1313', 0)
SELECT * FROM NewAuthors WHERE au_id = '999-99-9999'
-- Pulizia tabella
DELETE NewAuthors WHERE au_id = '999-99-9999'
DELETE AuditAuthors
GO
--<CUT HERE>--
Ciao,
--
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Vice-President of the Italian User Group for SQL Server
Founding Member of the Italian User Group for .NET
http://www.ghotz.com
http://www.ugiss.org