TRIGGER d'insertion après une requête INSERT

Version imprimable

Bonjour à tous,
Je n'avais jamais fait de Trigger auparavant et là je dois vous avouer que je suis perdu. :aie:

J'ai une table articles et une table url, comme vous l'aurez sûrement compris ces tables sont liées sur le principe q'un article peut avoir plusieurs url.

Voici la structure de la table articles:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE articles ( article_id INT IDENTITY(1,1) NOT NULL, id_produits_ INT NOT NULL, id_acces_ INT NOT NULL, id_titre_traduction_ INT NULL, id_commentaire_traduction_ INT NULL, titre VARCHAR(250) NULL, commentaire VARCHAR(8000) NULL, date_publication DATETIME NULL, date_maj DATETIME NULL, PRIMARY KEY(article_id), );
Voici la structure de la table url:
Code:

1 2 3 4 5 6 7 CREATE TABLE url ( id_url INT IDENTITY(1,1) NOT NULL, article_id_ INT NOT NULL, titre_url VARCHAR(200) NULL, lien_url VARCHAR(200) NULL, PRIMARY KEY(id_url), );
Je précise que j'ai créer une contrainte de clé étrangère à part sur articles.article_id=url.article_id_

Ce que je fais et souhaite faire pour le moment:

A partir d'un formulaire je récupère les données pour les 2 tables.
Je fais dans un premier temps ma requête d'insertion dans la table article
A l'aide du Trigger je génère une seconde requête d'insertion pour la table url en récupérant l'id de l'article créé précédemment.

J'espère avoir été assez claire.
Merci d'avance pour votre aide.

Je suis en train de faire queque chose comme ça

Code:

1
2
3
4
5
6
7
CREATE TRIGGER article_url ON [articles] AFTER INSERT
AS
SET NOCOUNT OFF 
 
SELECT MAX(article_id) FROM articles AS lastID
 
INSERT INTO [url] ([titre_id_],[titre_url],[lien_url]) VALUES (lastID,titre,lien)

Dite ce vous en pensé.

Bonjour,

Je pense avoir compris ce que vous voulez faire mais pas la façon dont vous l'avez implémenté.

Vous pouvez par exemple écrire :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TRIGGER TR_A_I_article_url ON dbo.articles AFTER INSERT AS BEGIN SET NOCOUNT ON INSERT INTO dbo.url ( article_id_, titre_url, lien_url ) SELECT article_id titre, 'http://monSite/' + titre + '.JPG' FROM INSERTED END
Dans un trigger AFTER INSERT, vous avez accès à la table virtuelle INSERTED, qui vous fournit les données que vous venez d'insérer dans la table.
INSERTED a strictement la même structure que la table cible de l'insertion.

Dans votre cas vous devez vous servir des données insérées dans article pour insérer dans url, c'est bien ça ?

Si vous spécifiez SET NOCOUNT OFF, vous allez retourner à l'application cliente le nombre de ligne affectées par le trigger.
Avec SET NOCOUNT ON, c'est l'inverse ;)

@++ ;)

03/04/2009, 11h59
nasimpat7

Merci elsuket pour la qualité de ta réponse, tu a compris exactement ce que je voulais faire.
Mais j'ai d'autres questions qui pourra te semblé un peu bette.
Où dois-je utiliser ce code?
A la suite de chaque requête, ce qui je crois est la solution?
ou
dois-je exécuter ce code une seule fois dans l'interface de gestion de ma Base de données?

Peut-tu m'expliquer:

Citation:

SELECT article_id
titre,
'http://monSite/' + titre + '.JPG'
FROM INSERTED

J'ai compris que cette requête récupère les données de la précédente requete.
Mais dans mon cas je n'ai besoin que de l'id. Le titre et le lien je les est déjà à partir de mon formulaire.

Merci encore:king:
03/04/2009, 12h11
elsuket

Si tu as déjà tout dans ton formulaire, alors n'utilise pas de trigger, ils sont gourmands en ressources.

Utilises plutôt une procédure stockée à laquelle tu passes tous les paramètres nécessaires pour ajouter un article proprement dans les deux tables.
Cette procédure stockée comportera donc deux instructions INSERT, la première dans articles, la seconde dans url.

@++ ;)
03/04/2009, 12h32
nasimpat7

J'ai pensé à cette solution, mais le fait qu'il y est une contrainte de clé étrangère ne va t-il pas posé problème?
Peut-être pas si je fais ma requête dans une transaction.
Et faudrait-il encore que je puisse récupérer l'id du dernier article créé dans le cadre de cette même transaction.

Citation:

Peut-être pas si je fais ma requête dans une transaction.

C'est exact.
Pour récupérer le dernier identifiant inséré dans la table articles :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 DECLARE @IDarticle INT INSERT INTO dbo.articles .... SELECT @lIDArticle = SCOPE_IDENTITY() INSERT INTO dbo.url ( ..., IDArticle, ... ) VALUES ( ..., @lIDArticle, ... )
@++ ;)

03/04/2009, 20h03
nasimpat7

Merci à toi elsuket pour tes réponses qui m'ont éclairées, je vais opter pour la transaction...:king:

OK, mais attention à bien la gérer :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE PROCEDURE Ps_Article_Ajoute
	@id_produits_ INT,
	@id_acces_ INT,
	@id_titre_traduction_ INT,
	@id_commentaire_traduction_ INT,
	@titre VARCHAR(250),
	@commentaire VARCHAR(8000),
	@date_publication DATETIME
AS
BEGIN
	DECLARE @IDarticle INT
 
	BEGIN TRY
		BEGIN TRANSACTION
			INSERT INTO dbo.articles 
			(
				id_produits_,
				id_acces_,
				id_titre_traduction_,
				id_commentaire_traduction_,
				titre,
				commentaire,
				date_publication,
				date_maj
			)
			VALUES
			(
				@id_produits_,
				@id_acces_,
				@id_titre_traduction_,
				@id_commentaire_traduction_,
				@titre,
				@commentaire,
				@date_publication,
				GETDATE()
			)
 
			SELECT @lIDArticle = SCOPE_IDENTITY()
 
			INSERT INTO dbo.url
			(
				article_id_ INT,
				titre_url,
				lien_url,
 
			)
			VALUES
			(
				@lIDArticle,
				@titre,
				'http://...'
			)
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		-- Soit vous avez une table pour la gestion d'erreurs et vous stockez l'erreur dedans
		-- Soit vous définissez vos propres messages d'erreur à l'aide de sp_addmessage
		-- Soit vous relancez l'erreur avec votre propre message
		DECLARE @errMsg NVARCHAR(1024)
		SELECT @errMsg = ERROR_MESSAGE()
 
		RAISERROR('Erreur lors de l''ajout de l''article %s : %s', 16, 1, @titre, @errMsg)
		ROLLBACK TRANSACTION
	END CATCH
END

La documentation de sp_addmessage est ici

@++ ;)

Bonjour elsuket,
Cette transaction est destinée à application .net / C#.
Je me suis donc fais une petite Class TransactInsert qui apparemment fonctionne et j'aurais aimé avoir ton avis.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
 
/// <summary>
/// Cette class à pour but de réaliser des transactions SQL,
/// afin de faire des requête INSERT multiples 
/// et de s'assurer de récupérer les dernières informations entrées dans la base de données.
/// Ainsi pour l'insertion d'une notice qui nécessite une url, il sera possible de créer un article
/// de type notice de récupérer l'ID de cet article et lier une url grace à cet ID.
/// </summary>
public class TransactionInsert
{
    int _id;
    string _titre;
    string _comment;
    string _access;
    string _url ;
    DateTime _date;
    string StrConn;
    string _message;
    string StrReq1;
    string StrReq2;
    string StrReq3;
 
    public TransactionInsert(int id, string titre, string comment, string access, string url, DateTime date)
	{
        this._id = id;
        this._titre = titre;
        this._comment = comment;
        this._access = access;
        this._url = url;
        this._date = date;
        this.StrConn = ConfigurationManager.ConnectionStrings["supportConnectionString"].ConnectionString;
    }
 
    public void InsertNotice ()
    {
        //on prépare et créé la connexion
        SqlConnection Conn = new SqlConnection(StrConn);
        //on créer la transaction
        SqlTransaction Trans = null;
 
        try
        {
            //on ouvre la connexion
            Conn.Open();
            //on initialise la transaction
            Trans = Conn.BeginTransaction();
 
            //chaine de la requête d'insertion dans table [articles]
            this.StrReq1 = "INSERT INTO [articles] ([id_produits_], [id_acces_], [titre], [commentaire], [type_notice], [date_publication]) VALUES (" + this._id + "," + this._access + ",'" + this._titre + "', '" + this._comment + "',1,'" + this._date + "')";
            //Requête INSERT dans la table articles dans le cadre de la transaction
            SqlCommand Req1 = new SqlCommand(StrReq1, Conn, Trans);
            //on execute la commande Req1
            Req1.ExecuteNonQuery();
 
            //on récupére l'ID du dernier article créé précédemment dans le cadre de la transaction
            //chaine de la requête qui permet de récupérer l'ID
            this.StrReq2 = "SELECT MAX(article_id) FROM articles";
            //requête SELECT de la table articles dans de  la cadre de la transaction
            SqlCommand Req2 = new SqlCommand(StrReq2, Conn, Trans);
            //on execute la commande Req2 grace à laquelle on récupère l'ID dans INT _article_id
            int _article_id = (int)Req2.ExecuteScalar();
 
            //insertion de l'url liée à l'article créer dans la cadre de la transaction
            this.StrReq3 = "INSERT INTO [url] (article_id_, titre_url, lien_url) VALUES (" + _article_id + ",'" + this._titre + "','" + this._url + "')";
            //requête INSERT dans la table url dans de le cadre de la transaction
            SqlCommand Req3 = new SqlCommand(StrReq3, Conn, Trans);
            //on execute la commande Req3
            Req3.ExecuteNonQuery();
 
            //on valide la transaction
            Trans.Commit();
            this._message = "Insertion effectuée avec succés";
 
        }
        catch (Exception err)
        {
            //on annule la transaction
            Trans.Rollback();
            this._message = err.Message+" 1: "+StrReq1+" 2: "+StrReq2+" 3: " +StrReq3;
        }
 
        finally
        {
            //on ferme la connexion
            Conn.Close();
        }
    }
 
    public string Message()
    {
        return this._message;
    }
}

Merci d'avance;)

06/04/2009, 14h20
elsuket

OK, mais que se passe-t-il si on démarre la transaction et que, supposons, la connexion tombe ?
On a commencé la transaction, le moteur de bases de données va attendre la validation ou l'annulation, et pendant ce temps la transaction reste ouverte, bloquant les ressources qui lui sont liées ...
Il y a beaucoup d'avantages à choisir une procédure stockée comme tu le verras dans ce billet plutôt que de passer une commande SQL ...
Cela simplifie ton code en C#, et rend la maintenance du code SQL plus simple pour toi et le DBA (pour les performances).

@++ ;)
06/04/2009, 15h45
nasimpat7

Les avantages d'une procédure stockée sont visiblement très intéressants.
Le problème est que je ne sais pas encore comment déployer tout ça.
J'ai donc commandé un bouquin de conception et réalisation de BD, qui parle notamment des procédures stockées.

Merci en tout cas merci à toi elsulnet pour le temps que m'a accordé.:king: