En quoi la fonction trigger_nestlevel assure une unicité d'un record

**randriano** · 31/10/2008, 10h59

Bonjour,

J'ai le code suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
CREATE TABLE [dbo].[MECA_TABLE] (
	[ID] [uniqueidentifier] PRIMARY KEY DEFAULT NEWID(), 
	[TableName] [nvarchar] (512) NOT NULL, 
	[FieldName] [nvarchar] (512), 
	[OptionName] [nchar] (50) NOT NULL,
	[OptionValue] [nvarchar] (512),
	[Currency] [int] NOT NULL,
	)
 
CREATE TRIGGER [dbo].[TRIGGER_UPDATE_Currency]
ON MECA_TABLE AFTER INSERT
AS
	IF ( (SELECT trigger_nestlevel() ) <= 1 )
    BEGIN 
	    DECLARE @_TableName nvarchar (50)
	    DECLARE @_FieldName nvarchar (50)
	    DECLARE @_OptionName nvarchar (50)
	    DECLARE @_OptionValue nvarchar (512)
	    DECLARE @_Currency int
	    DECLARE @_ID uniqueidentifier
 
	    DECLARE @_FieldNameLink nvarchar (54)
	    DECLARE @_FieldNameHint nvarchar (54)
	    DECLARE @_TableNameURL nvarchar (50)
	    DECLARE @_SelectType nvarchar (1)
	    SET @_SelectType = 'U'
 
	    DECLARE InsertedCursor CURSOR FOR
	    SELECT ID, TableName, FieldName, OptionName, OptionValue, Currency FROM inserted
 
	    OPEN InsertedCursor
	    FETCH NEXT FROM InsertedCursor INTO @_ID, @_TableName, @_FieldName, @_OptionName, @_OptionValue, @_Currency
 
	    WHILE (@@FETCH_STATUS = 0)
	    BEGIN
		    IF (@_OptionName = 'UserField')
			    DELETE FROM MECA_TABLE WHERE 
				    TableName = @_TableName AND
				    FieldName = @_FieldName AND
				    OptionName = 'UserField' AND
				    ID <> @_ID
 
		    IF ((@_OptionName = 'Hypertext') AND (@_OptionValue = '1'))
		    BEGIN
			    SET @_FieldNameLink = LTrim(RTrim(@_FieldName)) + 'LINK'
	            SET @_FieldNameHint = LTrim(RTrim(@_FieldName)) + 'HINT'
	            SET @_TableNameURL = LTrim(RTrim(@_TableName))
			    EXEC ('IF NOT EXISTS (SELECT name FROM syscolumns WHERE name = ''' + @_FieldNameLink + ''' AND id = (SELECT id FROM sysobjects WHERE name = ''' + @_TableNameURL + ''' AND type = ''' + @_SelectType + ''')) ALTER TABLE [dbo].[' + @_TableNameURL + '] ADD [' + @_FieldNameLink + '] [nvarchar] (256)')
			    EXEC ('IF NOT EXISTS (SELECT name FROM syscolumns WHERE name = ''' + @_FieldNameHint + ''' AND id = (SELECT id FROM sysobjects WHERE name = ''' + @_TableNameURL + ''' AND type = ''' + @_SelectType + ''')) ALTER TABLE [dbo].[' + @_TableNameURL + '] ADD [' + @_FieldNameHint + '] [nvarchar] (256)')
		        EXEC ('IF NOT EXISTS (SELECT * FROM MECA_TABLE WHERE TableName = ''' + @_TableNameURL +
		                                        ''' AND FieldName = ''' + @_FieldNameLink +
		                                        ''' AND OptionName = ''UserField'' ) INSERT INTO MECA_TABLE (TableName,FieldName,OptionName,OptionValue) values (''' +
		                                        @_TableNameURL + ''',''' + @_FieldNameLink + ''', ''UserField'', ''' + @_FieldNameLink + ''')')
			    EXEC ('IF NOT EXISTS (SELECT * FROM MECA_TABLE WHERE TableName = ''' + @_TableNameURL + 
			                                    ''' AND FieldName = ''' + @_FieldNameHint +
			                                    ''' AND OptionName = ''UserField'' ) INSERT INTO MECA_TABLE (TableName,FieldName,OptionName,OptionValue) values (''' +
			                                    @_TableNameURL + ''',''' + @_FieldNameHint + ''', ''UserField'', ''' + @_FieldNameHint + ''')')
			    EXEC ('IF NOT EXISTS (SELECT * FROM MECA_TABLE WHERE TableName = ''' + @_TableNameURL +
		                                        ''' AND FieldName = ''' + @_FieldNameLink +
		                                        ''' AND OptionName = ''Visible'' ) INSERT INTO MECA_TABLE (TableName,FieldName,OptionName,OptionValue) values (''' +
		                                        @_TableNameURL + ''',''' + @_FieldNameLink + ''', ''Visible'', 0)')
			    EXEC ('IF NOT EXISTS (SELECT * FROM MECA_TABLE WHERE TableName = ''' + @_TableNameURL +
			                                    ''' AND FieldName = ''' + @_FieldNameHint +
			                                    ''' AND OptionName = ''Visible'' ) INSERT INTO MECA_TABLE (TableName,FieldName,OptionName,OptionValue) values (''' +
			                                    @_TableNameURL + ''',''' + @_FieldNameHint + ''', ''Visible'', 0)')
		    END
 
		    UPDATE MECA_TABLE SET Currency = Currency - (Currency & @_Currency) WHERE
			    TableName = @_TableName AND
			    FieldName = @_FieldName AND
			    OptionName = @_OptionName AND
			    ID <> @_ID AND
			    (
				    ( OptionName <> 'SystemValue' AND OptionName <> 'UserValue') OR
				    ( OptionName = 'SystemValue' AND OptionValue = @_OptionValue ) OR
				    ( OptionName = 'UserValue' AND OptionValue = @_OptionValue )
			    )
 
		    FETCH NEXT FROM InsertedCursor INTO @_ID, @_TableName, @_FieldName, @_OptionName, @_OptionValue, @_Currency
	    END
 
	    CLOSE InsertedCursor
	    DEALLOCATE InsertedCursor
 
	    DELETE FROM MECA_TABLE WHERE Currency = 0
	END
	ELSE
	PRINT ('trigger_nestlevel > 1')
 
 
CREATE TRIGGER [dbo].[TRIGGER_Delete_URL]
ON MECA_TABLE AFTER DELETE
AS
	IF ( (SELECT trigger_nestlevel() ) <= 1 )
    BEGIN 
	    DECLARE @_TableName nvarchar (50)
	    DECLARE @_FieldName nvarchar (50)
	    DECLARE @_OptionName nvarchar (50)
	    DECLARE @_OptionValue nvarchar (512)
	    DECLARE @_Currency int
	    DECLARE @_ID uniqueidentifier
 
	    DECLARE @_FieldNameLink nvarchar (54)
	    DECLARE @_FieldNameHint nvarchar (54)
	    DECLARE @_TableNameURL nvarchar (50)
	    DECLARE @_SelectType nvarchar (1)
	    SET @_SelectType = 'U'
 
	    DECLARE DeletedCursor CURSOR FOR
	    SELECT ID, TableName, FieldName, OptionName, OptionValue, Currency FROM deleted
 
	    OPEN DeletedCursor
	    FETCH NEXT FROM DeletedCursor INTO @_ID, @_TableName, @_FieldName, @_OptionName, @_OptionValue, @_Currency
 
	    WHILE (@@FETCH_STATUS = 0)
	    BEGIN
		    IF ((@_OptionName = 'Hypertext') AND (@_OptionValue = '1'))
		    BEGIN
			    SET @_FieldNameLink = LTrim(RTrim(@_FieldName)) + 'LINK'
	            SET @_FieldNameHint = LTrim(RTrim(@_FieldName)) + 'HINT'
	            SET @_TableNameURL = LTrim(RTrim(@_TableName))
			    EXEC ('IF EXISTS (SELECT name FROM syscolumns WHERE name = ''' + @_FieldNameLink + ''' AND id = (SELECT id FROM sysobjects WHERE name = ''' + @_TableNameURL + ''' AND type = ''' + @_SelectType + ''')) ALTER TABLE [dbo].[' + @_TableNameURL + '] DROP COLUMN [' + @_FieldNameLink + ']')
			    EXEC ('IF EXISTS (SELECT name FROM syscolumns WHERE name = ''' + @_FieldNameHint + ''' AND id = (SELECT id FROM sysobjects WHERE name = ''' + @_TableNameURL + ''' AND type = ''' + @_SelectType + ''')) ALTER TABLE [dbo].[' + @_TableNameURL + '] DROP COLUMN [' + @_FieldNameHint + ']')
		    END
		    FETCH NEXT FROM DeletedCursor INTO @_ID, @_TableName, @_FieldName, @_OptionName, @_OptionValue, @_Currency
	    END
 
	    CLOSE DeletedCursor
	    DEALLOCATE DeletedCursor
	END
 
	ELSE
	PRINT ('trigger_nestlevel > 1')

Comment fonction trigger_nestlevel?

Lorsque je fais un insert dans cette table:
INSERT INTO MECA_TOOLS(TableName, FieldName, OptionName, OptionValue)
values ('MECA_TOOLS_TABLE', 'URL', 'Hypertext', '1')
Ca s'insère mais lorsque je l'exécute une seconde fois, ça ne s'insère pas alors qu'il n'est défini aucunes contraintes UNIQUE dans ce script

Rem: DB SQL SERVER 2000

**elsuket** · 03/11/2008, 23h14

Bonjour,

Ne prenez pas cela pour une réponse faite sur un ton ironique ou méchant mais votre trigger ne peut pas être performant :
- votre code est à la limite de l'inintelligible,
- vous avez utilisé un curseur,
- vous tentez de transformer un SGBDR en logiciel de traitement de texte.

Lorsque vous programmez des bases de données relationnelles, privilégiez absolument la production de code ensembliste (traitement d'un ensemble de tuples) au code itératif (traitement d'un tuple après l'autre) : banissez les curseurs.

A la lecture de votre code, on peut voir que votre modèle de données n'est pas adapté à ce que vous souhaitez qu'il supporte, sinon vous n'auriez pas besoin de modifier des tables lors d'une insertion.

Il est probable que ce ne soit pas vous qui ayez conçu le modèle de données, et que vous ayez du produire un tel code pour pallier au manque de rigueur de la personne qui l'a conçu.

Le code que je vous propose n'est en aucun cas une solution à votre problème, mais seulement un exemple pour vous aider à y voir un peu plus clair :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE TRIGGER [dbo].[TRIGGER_UPDATE_Currency]
ON MECA_TABLE AFTER INSERT
AS
BEGIN
	DECLARE @dummy VARCHAR(MAX)
 
	SELECT @dummy = ISNULL(@dummy, '') +
			 'DELETE FROM MECA_TABLE WHERE TableName = ' + TableName +
			 ' AND FieldName = ' + FieldName + 
			 ' AND OptionName = ''UserField'';'
	FROM INSERTED;
	EXEC (@dummy);
	-------------------------------------------------------------------------
	SELECT @dummy = 'IF NOT EXISTS ' +
			'(' +
				'SELECT C.name' + 
				' FROM syscolumns C' + 
				' JOIN sysobjects O ON C.id = O.id' + 
				' WHERE C.name = ''' + LTRIM(RTRIM(FieldName)) + 'LINK''' +
				' AND O.name = ''' + TableName +
				''' AND O.type = U' +
			')' +
			'BEGIN; ALTER TABLE dbo.' + TableName + ' ADD ''' + LTRIM(RTRIM(FieldName)) + 'LINK''' + ' NVARCHAR(256);'
	FROM INSERTED;
	EXEC (@dummy);
	---------------------------------------------------------------------------				
	SELECT @dummy = 'IF NOT EXISTS ' +
			'(' +
				'SELECT C.name' + 
				' FROM syscolumns C' + 
				' JOIN sysobjects O ON C.id = O.id' + 
				' WHERE C.name = ''' + LTRIM(RTRIM(FieldName)) + 'HINT''' +
				' AND O.name = ''' + TableName +
				''' AND O.type = U'
			')' +
			'BEGIN; ALTER TABLE dbo.' + TableName + ' ADD ''' + LTRIM(RTRIM(FieldName)) + 'HINT''' + ' NVARCHAR(256);'
	FROM INSERTED;
	EXEC (@dummy);
	----------------------------------------------------------------------------
	SELECT @dummy = 'IF NOT EXISTS ' +
			'(' + 
				'SELECT *'
				' FROM MECA_TABLE ' + 
				' WHERE TableName = ' + TableName +
				' AND FieldName = ' + LTRIM(RTRIM(FieldName)) + 'LINK''' +
				' AND OptionName = ''UserField''' +
			') ' +
			' INSERT INTO MECA_TABLE (TableName, FieldName, OptionName, OptionValue)' + 
			' VALUES (''' + TableName + ''', ''' + LTRIM(RTRIM(FieldName)) + 'LINK'', ''UserField'',''' +  LTRIM(RTRIM(FieldName)) + 'LINK'')'
	FROM INSERTED;
	EXEC (@dummy);
	-----------------------------------------------------------------------------
	SELECT @dummy = 'IF NOT EXISTS '
			'(' + 
				'SELECT *'
				' FROM MECA_TABLE ' + 
				' WHERE TableName = ' + TableName +
				' AND FieldName = ' + LTRIM(RTRIM(FieldName)) + 'HINT''' +
				' AND OptionName = ''UserField''' +
			') ' +
			' INSERT INTO MECA_TABLE (TableName, FieldName, OptionName, OptionValue)' + 
			' VALUES (''' + TableName + ''', ''' + LTRIM(RTRIM(FieldName)) + 'HINT'', ''UserField'',''' +  LTRIM(RTRIM(FieldName)) + 'HINT'')'
	FROM INSERTED;
	EXEC (@dummy);
	------------------------------------------------------------------------------
	SELECT @dummy = 'IF NOT EXISTS '
			'(' + 
				'SELECT *'
				' FROM MECA_TABLE ' + 
				' WHERE TableName = ' + TableName +
				' AND FieldName = ' + LTRIM(RTRIM(FieldName)) + 'LINK''' +
				' AND OptionName = ''UserField''' +
			') ' +
			' INSERT INTO MECA_TABLE (TableName, FieldName, OptionName, OptionValue)' + 
			' VALUES (''' + TableName + ''', ''' + LTRIM(RTRIM(FieldName)) + 'LINK'', ''Visible'', 0)'
	FROM INSERTED;
	EXEC (@dummy);
	-------------------------------------------------------------------------------
	SELECT @dummy = 'IF NOT EXISTS '
			'(' + 
				'SELECT *'
				' FROM MECA_TABLE ' + 
				' WHERE TableName = ' + TableName +
				' AND FieldName = ' + LTRIM(RTRIM(FieldName)) + 'HINT''' +
				' AND OptionName = ''UserField''' +
			 ') ' +
			' INSERT INTO MECA_TABLE (TableName, FieldName, OptionName, OptionValue)' + 
			' VALUES (''' + TableName + ''', ''' + LTRIM(RTRIM(FieldName)) + 'HINT'', ''Visible'', 0)'
	FROM INSERTED;
	EXEC (@dummy);
END

La fonction TRIGGER_NESTLEVEL() n'est utile que si votre trigger déclenche encore une fois celui-ci (votre trigger réinsèrerait par exemple un jeu de lignes dans votre table, qui déclenche votre trigger, et ainsi de suite, ...)

@++

**randriano** · 04/11/2008, 07h00

Envoyé par elsuket

La fonction TRIGGER_NESTLEVEL() n'est utile que si votre trigger déclenche encore une fois celui-ci (votre trigger réinsèrerait par exemple un jeu de lignes dans votre table, qui déclenche votre trigger, et ainsi de suite, ...)

@++

En effet, c'est ça le rôle de TRIGGER_NESTLEVEL(), dans mon cas, c'est utile quelquefois car le trigger after insert contient aussi des 4 INSERT sur la même table!!
Et désolé pour le dérangement mais c'est pas en fait cette fonction qui a assuré l'unicité d'un enregistrement de ce code qui n'est pas en fait le mien mais piqué

C'était ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
		    UPDATE MECA_TABLE SET Currency = Currency - (Currency & @_Currency) WHERE
			    TableName = @_TableName AND
			    FieldName = @_FieldName AND
			    OptionName = @_OptionName AND
			    ID <> @_ID AND
			    (
				    ( OptionName <> 'SystemValue' AND OptionName <> 'UserValue') OR
				    ( OptionName = 'SystemValue' AND OptionValue = @_OptionValue ) OR
				    ( OptionName = 'UserValue' AND OptionValue = @_OptionValue )
			    )
 
		    FETCH NEXT FROM InsertedCursor INTO @_ID, @_TableName, @_FieldName, @_OptionName, @_OptionValue, @_Currency
	    END
 
	    CLOSE InsertedCursor
	    DEALLOCATE InsertedCursor
 
	    DELETE FROM MECA_TABLE WHERE Currency = 0

En quoi la fonction trigger_nestlevel assure une unicité d'un record

Développement SQL Server

Discussions similaires

Partager

Partager