MSSQL 2000 - Nieuwe record toevoegen in view

[fdv99]

Ik kom er niet meer uit, heb verschillende howto's gelezen en geprobeerd, op GOT staat hier niet veel over, microsoft site afgespeurd, google geraadpleegd maar ben er nu al een paar dagen mee bezig en kom niet echt verder.

Wat ik heb: 2 tabellen en een view die de 2 tabellen laat zien

Tabel 1:

SQL:

CREATE TABLE [dbo].[algemeen] (
    [idnr] [int] IDENTITY (1, 1) NOT NULL ,
    [ordernummer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [opdrachtgever_naam] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [opdrachtgever_adres] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [opdrachtgever_postcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [opdrachtgever_plaats] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [werkomschrijving] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [order_gereed] [smalldatetime] NULL 
) ON [PRIMARY]

Tabel 2:

SQL:

CREATE TABLE [dbo].[tekenkamer] (
    [idnr] [int] IDENTITY (1, 1) NOT NULL ,
    [ordernummer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [werkvoorbereider] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tekenaar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [montage_week] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [montage_week_wordt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [teken_uren] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [wvb_uren] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [datum_op_tekenkamer] [smalldatetime] NULL ,
    [datum_start_tekenwerk] [smalldatetime] NULL ,
    [datum_zaaglijst_fabriek] [smalldatetime] NULL ,
    [datum_montage_map] [smalldatetime] NULL ,
    [profiel_systeem_1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [profiel_systeem_2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [profiel_systeem_3] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [vlakvulling_1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [vlakvulling_2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [vlakvulling_3] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [kleur_1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [kleur_2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [kleur_3] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [tekenkamer_memo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

View:

SQL:

CREATE VIEW view_order19991 AS
SELECT A.ordernummer AS Orderno, opdrachtgever_naam AS Opdrachtgever, werkomschrijving AS Werk, order_gereed AS Gereed, datum_op_tekenkamer AS [Datum op tek], montage_week AS Mont_week, montage_week_wordt AS Mont_wordt, teken_uren AS Tek_uren, wvb_uren, datum_start_tekenwerk AS [Start-tek], tekenaar, datum_zaaglijst_fabriek AS [Zaagl-fabr], datum_montage_map AS Montmap, tekenkamer_memo AS Opmerkingen, profiel_systeem_1 AS [Prof-syst 1], profiel_systeem_2 AS [Prof-syst 2], profiel_systeem_3 AS [Prof-syst 3], kleur_1 AS [Kleur 1], kleur_2 AS [Kleur 2], kleur_3 AS [Kleur 3], vlakvulling_1 AS [Vlakvulling 1], vlakvulling_2 AS [Vlakvulling 2], vlakvulling_3 AS [Vlakvulling 3], werkvoorbereider AS Coordinator
FROM algemeen A, tekenkamer T
WHERE A.ordernummer = T.ordernummer

Voor de gebruikers interface gebruik ik MS Access en heb de view gekoppeld door middel van ODBC. Tot hier werkt alles goed, de gebruikers kunnen gegevens bekijken, reports printen enz.

Nu komt het: Ik wil graag dat de gebruikers de data kunnen aanpassen (via Access dan, heb er mooie formulieren voor gemaakt) rechtstreeks in de view en ook nieuwe records kunnen toevoegen (weet dat het niet zo'n hele mooie oplossing is, de naam zegt het al "view" maar kan nu even niet anders).

Nu heb ik gelezen dat je dat kan doen door een trigger te maken die dan de "UPDATE" en "INSERT" statements afvangt.

Het updaten van record is me gelukt met deze trigger:

SQL:

CREATE TRIGGER tri_UPDATE_view_order19991 ON view_order19991
INSTEAD OF UPDATE
AS
UPDATE algemeen
SET ordernummer = i.Orderno, opdrachtgever_naam = i.Opdrachtgever, werkomschrijving = i.Werk, order_gereed = i.Gereed
FROM inserted i
INNER JOIN algemeen ON i.Orderno = algemeen.ordernummer

UPDATE tekenkamer
SET datum_op_tekenkamer = i.[Datum op tek], montage_week = i.Mont_week, montage_week_wordt = i.Mont_wordt, teken_uren = i.Tek_uren, Wvb_uren = i.Wvb_uren, datum_start_tekenwerk = i.[Start-tek], tekenaar = i.tekenaar, datum_zaaglijst_fabriek = i.[Zaagl-fabr], datum_montage_map = i.Montmap, tekenkamer_memo = i.Opmerkingen, profiel_systeem_1 = i.[Prof-syst 1], profiel_systeem_2 = i.[Prof-syst 2], profiel_systeem_3 = i.[Prof-syst 3], kleur_1 = i.[Kleur 1], kleur_2 = i.[Kleur 2], kleur_3 = i.[Kleur 3], vlakvulling_1 = i.[Vlakvulling 1], vlakvulling_2 = i.[Vlakvulling 2], vlakvulling_3 = i.[Vlakvulling 3], werkvoorbereider = i.Coordinator
FROM inserted i
INNER JOIN tekenkamer ON i.Orderno = tekenkamer.ordernummer

Alleen lukt het me niet om de insert trigger voor elkaar te krijgen zodat je ook nieuwe records kan toevoegen. Heb tot nu toe dit:

SQL:

CREATE TRIGGER tri_INSERT_view_order19991 ON view_order19991
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO algemeen
SELECT Orderno,Opdrachtgever, Werk, Gereed
FROM inserted

INSERT INTO tekenkamer
SELECT Orderno,[Datum op tek],Mont_week,Mont_wordt,Tek_uren,wvb_uren,[Start-tek],tekenaar,[Zaagl-fabr],Montmap,Opmerkingen,[Prof-syst 1],[Prof-syst 2],[Prof-syst 3],[Kleur 1],[Kleur 2],[Kleur 3],[Vlakvulling 1],[Vlakvulling 2],[Vlakvulling 3],[Coordinator]
FROM inserted
END

Als ik deze code uitvoer in de SQL query Analyzer (rechtstreeks op de SQL server dus kan niet aan de connectie met Access liggen) krijg ik volgende foutmelding:

code:

Server: Msg 213, Level 16, State 4, Procedure tri_INSERT_view_order19991, Line 6
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 206, Level 16, State 1, Procedure tri_INSERT_view_order19991, Line 10
Operand type clash: text is incompatible with smalldatetime

Ik snap er helemaal niks meer van, zoals je ziet zijn er in de tabel 'algemeen' wel meer velden die ik in de view niet update, zoals een idnr die op auto increment staat, de rest van de niet geupdate velden staat op allow NULL, weet niet of dit het probleem is?

Wie o wie weet wat er fout is aan de laatste trigger of herkent de foutmelding

[whoami]

De foutmelding zegt het al: je wil meer of minder waardes aan die tabel toevoegen dan mogelijk is.

Schrijf eens je INSERT statement zo:

code:

INSERT INTO tabelnaam
( veldnaam1, veldnaam2, veldnaam3)
SELECT veld1, veld2, veld3 FROM inserted

Dus, je moet ook de namen van de velden die in je tabel zitten, aangeven. Zo ben je ook zeker dat de juiste waarde in het juiste veld terecht komt.

Aangezien jij die velden niet list , zal SQL Server gewoon voor ieder veld dat je in je tabel hebt, een waarde verwachten. En hij verwacht dan ook dat je die waardes geeft, in de volgorde waarop hij z'n veldn heeft. Dat doe jij dus niet, dus Sql Server kan -logisch- ook niets aanvangen met je insert.
Je moet dus aangeven voor welke velden je een waarde geeft.

[ Voor 32% gewijzigd door whoami op 14-02-2006 15:31 ]

[fdv99]

Een stap verder!

Hij accepteerd nu de trigger en slaat hem op (het klopte zoals je zei Whoami) maar als ik handmatig in de view (in de enterprise manager) een record probeer toe te voegen krijg ik volgende foutmelding:



Het klopt ook dat het veld "ordernummer" in de tabel "tekenkamer" niet leeg mag zijn maar ik probeer de waarde die in "Orderno" staat (vanuit inserted) daarin op te slaan, net zoals ik doe in de tabel algemeen. In die tabel werkt het wel dus volgens mij heeft "Orderno" wel een waarde

[ Voor 14% gewijzigd door fdv99 op 14-02-2006 16:13 ]

[Verwijderd]

Wat is nu je code? De foutmelding is duidelijk genoeg mijns inziens, je probeert toch echt een NULL-value te inserten...

[fdv99]

Oh ja, vergeten

de code van de trigger is nu:

SQL:

CREATE TRIGGER tri_INSERT_view_order19991 ON view_order19991
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO algemeen
(ordernummer, opdrachtgever_naam, werkomschrijving, order_gereed)
SELECT Orderno,Opdrachtgever, Werk, Gereed
FROM inserted

INSERT INTO tekenkamer
(ordernummer, datum_op_tekenkamer, montage_week, montage_week_wordt, teken_uren, wvb_uren, datum_start_tekenwerk, tekenaar, datum_zaaglijst_fabriek, datum_montage_map, tekenkamer_memo, profiel_systeem_1, profiel_systeem_2, profiel_systeem_3, kleur_1, kleur_2, kleur_3, vlakvulling_1, vlakvulling_2, vlakvulling_3, werkvoorbereider)
SELECT Orderno,[Datum op tek],Mont_week,Mont_wordt,Tek_uren,wvb_uren,[Start-tek],tekenaar,[Zaagl-fabr],Montmap,Opmerkingen,[Prof-syst 1],[Prof-syst 2],[Prof-syst 3],[Kleur 1],[Kleur 2],[Kleur 3],[Vlakvulling 1],[Vlakvulling 2],[Vlakvulling 3],[Coordinator]
FROM inserted
END

[fdv99]

Nog even voor de volledigheid, ik ben er eindelijk uit!! Ik heb het nummer wat hij in 2 verschillende tabellen moet plaatsten in een variabel gezet en nu werkt ie!!

Dit is de trigger geworden:

SQL:

CREATE TRIGGER tri_INSERT_view_order19991 ON view_order19991
INSTEAD OF INSERT
AS
BEGIN
DECLARE @order varchar(30)
SET @order = (SELECT i.Orderno FROM inserted i)
SET NOCOUNT ON
INSERT INTO algemeen
(ordernummer, opdrachtgever_naam, werkomschrijving, order_gereed)
SELECT @order, i.Opdrachtgever, i.Werk, i.Gereed
FROM inserted i

INSERT INTO tekenkamer
(ordernummer, datum_op_tekenkamer, montage_week, montage_week_wordt, teken_uren, wvb_uren, datum_start_tekenwerk, tekenaar, datum_zaaglijst_fabriek, datum_montage_map, tekenkamer_memo, profiel_systeem_1, profiel_systeem_2, profiel_systeem_3, kleur_1, kleur_2, kleur_3, vlakvulling_1, vlakvulling_2, vlakvulling_3, werkvoorbereider)
SELECT @order, i.[Datum op tek], i.Mont_week, i.Mont_wordt, i.Tek_uren, i.wvb_uren, i.[Start-tek], i.tekenaar, i.[Zaagl-fabr], i.Montmap, i.Opmerkingen, i.[Prof-syst 1], i.[Prof-syst 2], i.[Prof-syst 3], i.[Kleur 1], i.[Kleur 2], i.[Kleur 3], i.[Vlakvulling 1], i.[Vlakvulling 2], i.[Vlakvulling 3], i.[Coordinator]
FROM inserted i
END

[kenneth]

Minimale aanvulling:

SQL:

SET @order = (SELECT i.Orderno FROM inserted i) 
// kan ook gewoon als:
SELECT @order = i.Orderno FROM inserted i