MSSQL: AFTER Insert Trigger verstehen ...

[jensw_2000]

Ich habe bisher noch nie Trigger verwendet und brauche mal einen kleinen Denkanstoß.

Ich habe mir eine einfache DB-basierte Rechteverwaltung gebastelt die nach folgenden Schema funktioniert:

SQL-Code:

Tabelle Usergruppen

ID        UniqueIdentifier
GrpName   VarChar(20)

ID                                        GrpName 
-----------------------------------------------------------
{2FCE0D68-424F-4618-A7A5-144A60586273}    User Lesen/Schreiben
{23450D65-4454-6FD8-A544-435343553BC1}    Admins
-----------------------------------------------------------

Tabelle ZugriffsObjekte

ID             UniqueIdentifier
Beschreibung   VarChar(40)
VCL_Name       VarChar(30)

ID                                        Beschreibung             VCL_Name
-----------------------------------------------------------------------------------------
{93989981-EE88-4BFC-828D-E04840AD642A}    Benutzerverwaltung       act_Benutzerverwaltung
{74971F6D-33B9-471B-8939-26A90637B83C}    Datei öffnen             act_FileOpen
{75DDA832-8237-4BF1-9418-5436BD33B644}    Datei speichern          act_FileSave 
-----------------------------------------------------------------------------------------


Tabelle ACL_Usergruppen

ID_UserGruppen        UniqueIdentifier
ID_ZugriffsObjekte    UniqueIdentifier


ID_UserGruppen                            ID_ZugriffsObjekte   
--------------------------------------------------------------------------------
{2FCE0D68-424F-4618-A7A5-144A60586273}    {74971F6D-33B9-471B-8939-26A90637B83C}    -- alle Öffnen --
{2FCE0D68-424F-4618-A7A5-144A60586273}    {75DDA832-8237-4BF1-9418-5436BD33B644}    -- alle Speichern --
{2FCE0D68-424F-4618-A7A5-144A60586273}    {93989981-EE88-4BFC-828D-E04840AD642A}    -- Admins Benutzerverw. --
--------------------------------------------------------------------------------

Das Prinzip dürfte klar sein.

In meinem Programm nehme ich die GruppenID des Benutzer und suche nach den zugeordneten ZugriffsObjekten.
Alle Actions in meiner ActionList die nicht gefunden werden, werden deaktiviert ...


Jetzt möchte ich folgendes erreichen:

Wenn eine neue Benutzergruppe erstellt wird, sollen dieser Gruppe automatisch Standardrechte zugewiesen werden (Datei öffnen darf jeder).

Dazu brauche ich einen Eintrag in der ACL_Usergruppen mit ID_UserGruppen"neu", ID_ZugriffsObjekte"DateiÖffnen".

Ich habe versucht mir einen Insert Trigger auf der Tabelle Usergruppen zu bauen, aber irgendwie fehlt mir da ein Stück Verständnis ...

Hier der klägliche Versuch:

SQL-Code:

CREATE TRIGGER Set_ACL_DEFAULTS ON [dbo].[UserGruppen] 
FOR INSERT
AS

DECLARE @GRP_ID UniqueIdentifier
DECLARE @OBJ_ID UniqueIdentifier

SET @GRP_ID = dbo.Usergruppen.ID  -- sollte doch die ID der gerade erstellten Gruppe werden 
                                  -- SQL meckert aber, das der Präfix dbo.UserGruppen falsch ist
                                  -- nur ID ohne dbo.UserGruppen geht auch nicht (Feld 'ID' nicht gefunden)

SET @OBJ_ID = SELECT ID FROM dbo.ZugriffsObjekte WHERE VCL_Name = 'act_FileOpen'

INSERT INTO ACL_Usergruppen (ID_UserGruppen, ID_ZugriffsObjekte) 
                     VALUES (@GRP_ID,@OBJ_ID)

Was ist da falsch und wie geht es richtig ... ?

Danke,
Jens

[Jelly]

In Triggern stehen dir die Tabellen Inserted und Deleted zur Vefügung. Inserted enthält in deinem Fall dein gerade eingefügten Record in ACL_Usergruppen. Deine ID würdest du dann z.B. so erhalten:
set @ID = select ID from Inserted

[jensw_2000]

Geil, das geht !
Muss aber eine kleine Änderung rein.

SET @GRP_ID = (select ID from Inserted)
SET @OBJ_ID = (SELECT ID FROM dbo.ZugriffsObjekte WHERE VCL_Name = 'act_FileOpen')

Danke

[clues1]

darf ich dir noch ein paar Perfomance tipps geben?

Wenn dieser SQL Code immer der gleiche ist. Nimm ein View zur Hilfe. Spart Zugriffsresourcen.
Falls du aber VCL_Name immer mal änderst, dann mache eine Funktion. Dadurch kann der MS SQL Server Statistiken erstellen und gewinnst an Performance. Wenn die Tabelle view_ZugriffsObjekte selten geändert wird, merk sicher der MS SQL Server die Positionen in Sogenannten Statistiken.

SQL-Code:

SET @OBJ_ID = SELECT ID FROM dbo.ZugriffsObjekte WHERE VCL_Name = 'act_FileOpen'

-- SET @OBJ_ID = (SELECT ID FROM dbo.view_ZugriffsObjekte) 

-- CREATE VIEW dbo.view_ZugriffsObjekte
-- AS
--   SELECT ID FROM dbo.ZugriffsObjekte WHERE VCL_Name = 'act_FileOpen'
-- GO

[jensw_2000]

@clues1

Wenn das ganze mal fertig ist, baue ich noch 2 Tabellen dazu.
Eine für die Standard-Gruppen für neu angelegte Benutzer und eine für die Standard-Rechte neu angelegter Gruppen.

Dann werde ich deinen Vorschlag mit umsetzen
Wahrscheinlich mit einer Funktion, die mir als Result eine Table liefert, welche ich dann im AFTER INSERT Trigger einfach in die "Verknüpfungstabelle" schreibe.

Danke für den Tip....



[edit]

Werde mit wohl demnächst mal 2 Bücher kaufen

1. SQL Für Dummies die trotzdem alles probieren wollen
und
2. SQL für Dummies die mit Hilfe der DP was tolles gebastelt haben und dies nun optimieren wollen


[/edit]

[clues1]

kein Problem.
PS, die bücher habe ich schon kosten so 500€

[Robert_G]

Hat das einen tieferen Sinn, dass du über GIUDs verknüpfst?
Numerische Schlüssel sind massiv schneller.

[jensw_2000]

Ja,
die Datenbank soll, wenn das Programm mal fertig ist, über 12 Standorte repliziert werden. Der SQL Server legt bei der Einrichtung des Publishers zwar von sich aus eine Spalte RowGUID an um eine eindeutige Replikations-ID bereitzustellen, aber das reicht halt nicht.
Ein Primärschlüssel mit einem Autoinc (Identity) Wert ist bei langsamen Replikationsinvervallen (oder gar bei längerem Ausfall einer WAN Strecke) tödlich und ein generierter PK aus Standort+Identity warscheinlich genauso langsam wie der UniqueIdentifier.


Die Performance sackt übrigens garnicht so stark ab wie man denkt.
Ich habe zur Zeit des Datenbankentwurfs mal paar Tests mit je 500.000 Datensätzen gemacht und dabei ist der GUID-PK je nach Komplexität der Abfrage zwischen 3% und 8% langsamer.
Damit kann ich besser leben als mit PK Konflikten ...

[clues1]

Da du MS SQL Server benutzt, wird die performance fast garnicht leiden, da dies GUID irgend wie verknüpftwerden mit irgen eine Algo, der Extrem schnell ist.