469,920 Members | 2,479 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,920 developers. It's quick & easy.

sql server 2000 trigger

Hi,
can someone tell how to write a Trigger; I am familiar with Sybase Sql
Anywhere trigger syntax.
Actually I have three tables MEMBER, CONTRACT and PAYMENT

I need to update the MEMBER.BALANCE once the PAYMENT.AMOUNT is INSERTED
where PAYEMENT.CONTRAC_ID = CONTRACT.CONTRAC_ID
and CONTRAT.MEMBER_ID = MEMBRE.MEMBER_ID

I have more TRIGGERS to write, but with a good example it would be great

In fact, send me as many examples as you can

Thanks

Fernand

fe****************@videotron.ca
Jul 20 '05 #1
9 7837
"Fernand St-Georges" <fe****************@videotron.ca> wrote in message news:<LZ********************@wagner.videotron.net> ...
Hi,
can someone tell how to write a Trigger; I am familiar with Sybase Sql
Anywhere trigger syntax.
Actually I have three tables MEMBER, CONTRACT and PAYMENT

I need to update the MEMBER.BALANCE once the PAYMENT.AMOUNT is INSERTED
where PAYEMENT.CONTRAC_ID = CONTRACT.CONTRAC_ID
and CONTRAT.MEMBER_ID = MEMBRE.MEMBER_ID

I have more TRIGGERS to write, but with a good example it would be great

In fact, send me as many examples as you can

Thanks

Fernand

fe****************@videotron.ca


The AFTER trigger syntax should be more or less the same as Sybase. In
any case, the CREATE TRIGGER syntax entry in Books Online has 6
examples - if you're still having problems after checking that,
perhaps you could post your code and explain exactly where you're
having a problem.

Simon
Jul 20 '05 #2


I haven't found anything there that suits me. Se if I write a trigger
like Sybase
it would go like this

CREATE TRIGGER MONTANT_VERSEMENT ON [dbo].[VERSERMENT]
FOR INSERT
referencing new as new_VERSEMENT
AS
begin
declare @solde_membre numeric(5,2)
declare @montant_vers numeric(5,2)

select MEMBRE.SOLDE = @solde_membre from [dbo].[MEMBRE]
where VERSEMENT.NO_CONTRAT = CONTRAT.NO_CONTRAT
and CONTRAT.NO_MEMBRE = MEMBRE.NO_MEMBRE

select VERSEMENT.MONTANT = @montant_vers

UPDATE [dbo].[MEMBRE]
SET [dbo].[MEMBRE].SOLDE = @solde_membre + @montant_vers
go

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

"fernand st-georges" <fe****************@videotron.ca> wrote in message
news:40***********************@news.frii.net...


I haven't found anything there that suits me. Se if I write a trigger
like Sybase
it would go like this

CREATE TRIGGER MONTANT_VERSEMENT ON [dbo].[VERSERMENT]
FOR INSERT
referencing new as new_VERSEMENT
AS
begin
declare @solde_membre numeric(5,2)
declare @montant_vers numeric(5,2)

select MEMBRE.SOLDE = @solde_membre from [dbo].[MEMBRE]
where VERSEMENT.NO_CONTRAT = CONTRAT.NO_CONTRAT
and CONTRAT.NO_MEMBRE = MEMBRE.NO_MEMBRE

select VERSEMENT.MONTANT = @montant_vers

UPDATE [dbo].[MEMBRE]
SET [dbo].[MEMBRE].SOLDE = @solde_membre + @montant_vers
go

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Assuming that NO_CONTRAT is the primary key for dbo.VERSEMENT, then this
looks like it may be what you want, although I'm not sure:

CREATE TRIGGER MONTANT_VERSEMENT ON dbo.VERSEMENT
FOR INSERT
AS

if @@rowcount = 0
return

UPDATE dbo.MEMBRE
SET SOLDE = SOLDE + V.MONTANT
FROM dbo.MEMBRE M
join CONTRAT C
on M.NO_MEMBRE = C.NO_MEMBRE

join dbo.VERSEMENT V
on V.NO_CONTRAT = C.NO_CONTRAT

join inserted i
ON i.NO_CONTRAT = V.NO_CONTRAT
go

Hopefully that will be close enough to get you started, but if not then
please consider posting DDL for the relevant tables, including keys etc.

Simon
Jul 20 '05 #4


/************************************************** *********
Génération du DDL
Schéma MRD : "Projet 1.0"
Fichier MRD : "(1) gymnase.rdm"
Généré le : 2004-01-22 14:40:38
Par : l'Interface MRD-SQL-Server 2.7.2.0
************************************************** *********/

/************************************************** *********
ÉNONCÉS DROP
************************************************** *********/

/************************************************** *********
Contrainte de clé étrangère "FK_MEMBRE"
************************************************** *********/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_MEMBRE
GO

/************************************************** *********
Contrainte de clé étrangère "FK_TYPE_CONTRAT"
************************************************** *********/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_TYPE_CONTRAT
GO

/************************************************** *********
Contrainte de clé étrangère "FK_DUREE_CONTRAT"
************************************************** *********/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_DUREE_CONTRAT
GO

/************************************************** *********
Contrainte de clé étrangère "FK_APPROCHE_PROMOT"
************************************************** *********/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_APPROCHE_PROMOT
GO

/************************************************** *********
Contrainte de clé étrangère "MUNI_FK"
************************************************** *********/

ALTER TABLE MEMBRE
DROP CONSTRAINT MUNI_FK
GO

/************************************************** *********
Contrainte de clé étrangère "FK_CONTRAT"
************************************************** *********/

ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_CONTRAT
GO

/************************************************** *********
Contrainte de clé étrangère "FK_TYPE_PAIEMENT"
************************************************** *********/

ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_TYPE_PAIEMENT
GO

/************************************************** *********
Contrainte de clé étrangère "FK_MODE_PAIEMENT"
************************************************** *********/

ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_MODE_PAIEMENT
GO

/************************************************** *********
Contrainte de clé primaire "PK_APPROCHE_PROMOT"
************************************************** *********/

ALTER TABLE APPROCHE_PROMOTION
DROP CONSTRAINT PK_APPROCHE_PROMOT
GO

/************************************************** *********
Table "APPROCHE_PROMOTION"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'APPROCHE_PROMOTION')
BEGIN
DROP TABLE APPROCHE_PROMOTION
END
GO

/************************************************** *********
Contrainte de clé primaire "MEMBER_PK"
************************************************** *********/

ALTER TABLE CONTRAT
DROP CONSTRAINT MEMBER_PK
GO

/************************************************** *********
Table "CONTRAT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'CONTRAT')
BEGIN
DROP TABLE CONTRAT
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_DUREE_CONTRAT_P"
************************************************** *********/

ALTER TABLE DUREE_CONTRAT
DROP CONSTRAINT PK_DUREE_CONTRAT_P
GO

/************************************************** *********
Table "DUREE_CONTRAT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'DUREE_CONTRAT')
BEGIN
DROP TABLE DUREE_CONTRAT
END
GO

/************************************************** *********
Contrainte de clé primaire "MEMBRE_PK"
************************************************** *********/

ALTER TABLE MEMBRE
DROP CONSTRAINT MEMBRE_PK
GO

/************************************************** *********
Table "MEMBRE"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MEMBRE')
BEGIN
DROP TABLE MEMBRE
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_MODE_PAIEMENT_P"
************************************************** *********/

ALTER TABLE MODE_PAIEMENT
DROP CONSTRAINT PK_MODE_PAIEMENT_P
GO

/************************************************** *********
Table "MODE_PAIEMENT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MODE_PAIEMENT')
BEGIN
DROP TABLE MODE_PAIEMENT
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_MUNICIPALITE"
************************************************** *********/

ALTER TABLE MUNICIPALITE
DROP CONSTRAINT PK_MUNICIPALITE
GO

/************************************************** *********
Table "MUNICIPALITE"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MUNICIPALITE')
BEGIN
DROP TABLE MUNICIPALITE
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_TYPE_CONTRAT_PR"
************************************************** *********/

ALTER TABLE TYPE_CONTRAT
DROP CONSTRAINT PK_TYPE_CONTRAT_PR
GO

/************************************************** *********
Table "TYPE_CONTRAT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'TYPE_CONTRAT')
BEGIN
DROP TABLE TYPE_CONTRAT
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_TYPE_PAIEMENT_P"
************************************************** *********/

ALTER TABLE TYPE_PAIEMENT
DROP CONSTRAINT PK_TYPE_PAIEMENT_P
GO

/************************************************** *********
Table "TYPE_PAIEMENT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'TYPE_PAIEMENT')
BEGIN
DROP TABLE TYPE_PAIEMENT
END
GO

/************************************************** *********
Contrainte de clé primaire "PK_VERSERMENT_PRIM"
************************************************** *********/

ALTER TABLE VERSERMENT
DROP CONSTRAINT PK_VERSERMENT_PRIM
GO

/************************************************** *********
Table "VERSERMENT"
************************************************** *********/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'VERSERMENT')
BEGIN
DROP TABLE VERSERMENT
END
GO

/************************************************** *********
ÉNONCÉS CREATE
************************************************** *********/

/************************************************** *********
Table "APPROCHE_PROMOTION"
************************************************** *********/

CREATE TABLE APPROCHE_PROMOTION
(
APPROCHE_PROMOTION varchar(50) NOT
NULL
)
GO

ALTER TABLE APPROCHE_PROMOTION
ADD CONSTRAINT PK_APPROCHE_PROMOT
PRIMARY KEY (APPROCHE_PROMOTION)
GO

/************************************************** *********
Table "CONTRAT"
************************************************** *********/

CREATE TABLE CONTRAT
(
NUMERO_CONTRAT numeric(4) NOT
NULL,
DATE_DEBUT datetime
NULL,
PAYE bit
NULL,
MONTANT numeric(10,2)
NULL,
DATE_PAIEMENT_FINA datetime
NULL,
NUMERO_MEMBRE numeric(4) NOT
NULL,
ID_TYPE_CONTRAT numeric(4) NOT
NULL,
APPROCHE_PROMOTION varchar(50) NOT
NULL,
DUREE_CONTRAT varchar(15) NOT
NULL
)
GO

ALTER TABLE CONTRAT
ADD CONSTRAINT MEMBER_PK
PRIMARY KEY (NUMERO_CONTRAT)
GO

/************************************************** *********
Table "DUREE_CONTRAT"
************************************************** *********/

CREATE TABLE DUREE_CONTRAT
(
DUREE_CONTRAT varchar(15) NOT
NULL
)
GO

ALTER TABLE DUREE_CONTRAT
ADD CONSTRAINT PK_DUREE_CONTRAT_P
PRIMARY KEY (DUREE_CONTRAT)
GO

/************************************************** *********
Table "MEMBRE"
************************************************** *********/

CREATE TABLE MEMBRE
(
NUMERO_MEMBRE numeric(4) NOT
NULL,
TITRE varchar(4)
NULL,
NOM varchar(45)
NULL,
ADRESSE varchar(30)
NULL,
MAUVAIS_P bit
NULL,
CODE_POSTAL char(7)
NULL,
TELEPHONE char(12)
NULL,
FAX char(12)
NULL,
COURRIER_E char(30)
NULL,
SOLDE numeric(10,2)
NULL,
PHOTO varchar(100)
NULL,
DATE_DE_NAISSANCE datetime
NULL,
TELEPHONE_TRAVAIL char(12)
NULL,
NOTES varchar(200)
NULL,
NAS varchar(11)
NULL,
PRENOM varchar(45)
NULL,
NO_MUNICIPALITE numeric(4) NOT
NULL
)
GO

ALTER TABLE MEMBRE
ADD CONSTRAINT MEMBRE_PK
PRIMARY KEY (NUMERO_MEMBRE)
GO

/************************************************** *********
Table "MODE_PAIEMENT"
************************************************** *********/

CREATE TABLE MODE_PAIEMENT
(
MODE_PAIEMENT varchar(25) NOT
NULL
)
GO

ALTER TABLE MODE_PAIEMENT
ADD CONSTRAINT PK_MODE_PAIEMENT_P
PRIMARY KEY (MODE_PAIEMENT)
GO

/************************************************** *********
Table "MUNICIPALITE"
************************************************** *********/

CREATE TABLE MUNICIPALITE
(
NO_MUNICIPALITE numeric(4) NOT
NULL,
MUNICIPALITE varchar(15)
NULL
)
GO

ALTER TABLE MUNICIPALITE
ADD CONSTRAINT PK_MUNICIPALITE
PRIMARY KEY (NO_MUNICIPALITE)
GO

/************************************************** *********
Table "TYPE_CONTRAT"
************************************************** *********/

CREATE TABLE TYPE_CONTRAT
(
ID_TYPE_CONTRAT numeric(4) NOT
NULL,
TYPE_CONTRAT varchar(45) NOT
NULL
)
GO

ALTER TABLE TYPE_CONTRAT
ADD CONSTRAINT PK_TYPE_CONTRAT_PR
PRIMARY KEY (ID_TYPE_CONTRAT)
GO

/************************************************** *********
Table "TYPE_PAIEMENT"
************************************************** *********/

CREATE TABLE TYPE_PAIEMENT
(
TYPE_PAIEMENT varchar(45) NOT
NULL
)
GO

ALTER TABLE TYPE_PAIEMENT
ADD CONSTRAINT PK_TYPE_PAIEMENT_P
PRIMARY KEY (TYPE_PAIEMENT)
GO

/************************************************** *********
Table "VERSERMENT"
************************************************** *********/

CREATE TABLE VERSERMENT
(
NO_VERSEMENT numeric(4) NOT
NULL,
DATE_VERSEMENT datetime
NULL,
MONTANT numeric(10,2)
NULL,
NUMERO_CONTRAT numeric(4) NOT
NULL,
TYPE_PAIEMENT varchar(45) NOT
NULL,
MODE_PAIEMENT varchar(25) NOT
NULL
)
GO

ALTER TABLE VERSERMENT
ADD CONSTRAINT PK_VERSERMENT_PRIM
PRIMARY KEY (NO_VERSEMENT)
GO

/************************************************** *********
Les clés étrangères de la table "CONTRAT"
************************************************** *********/

ALTER TABLE CONTRAT
ADD CONSTRAINT FK_MEMBRE
FOREIGN KEY (NUMERO_MEMBRE)
REFERENCES MEMBRE (NUMERO_MEMBRE)
GO

ALTER TABLE CONTRAT
ADD CONSTRAINT FK_TYPE_CONTRAT
FOREIGN KEY (ID_TYPE_CONTRAT)
REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT)
GO

ALTER TABLE CONTRAT
ADD CONSTRAINT FK_DUREE_CONTRAT
FOREIGN KEY (DUREE_CONTRAT)
REFERENCES DUREE_CONTRAT (DUREE_CONTRAT)
GO

ALTER TABLE CONTRAT
ADD CONSTRAINT FK_APPROCHE_PROMOT
FOREIGN KEY (APPROCHE_PROMOTION)
REFERENCES APPROCHE_PROMOTION (APPROCHE_PROMOTION)
GO

/************************************************** *********
Les clés étrangères de la table "MEMBRE"
************************************************** *********/

ALTER TABLE MEMBRE
ADD CONSTRAINT MUNI_FK
FOREIGN KEY (NO_MUNICIPALITE)
REFERENCES MUNICIPALITE (NO_MUNICIPALITE)
GO

/************************************************** *********
Les clés étrangères de la table "VERSERMENT"
************************************************** *********/

ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_CONTRAT
FOREIGN KEY (NUMERO_CONTRAT)
REFERENCES CONTRAT (NUMERO_CONTRAT)
GO

ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_TYPE_PAIEMENT
FOREIGN KEY (TYPE_PAIEMENT)
REFERENCES TYPE_PAIEMENT (TYPE_PAIEMENT)
GO

ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_MODE_PAIEMENT
FOREIGN KEY (MODE_PAIEMENT)
REFERENCES MODE_PAIEMENT (MODE_PAIEMENT)
GO

/* Fin
*/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5
>
Assuming that NO_CONTRAT is the primary key for dbo.VERSEMENT, then this
looks like it may be what you want, although I'm not sure:

CREATE TRIGGER MONTANT_VERSEMENT ON dbo.VERSEMENT
FOR INSERT
AS

if @@rowcount = 0
return

UPDATE dbo.MEMBRE
SET SOLDE = SOLDE + V.MONTANT
FROM dbo.MEMBRE M
join CONTRAT C
on M.NO_MEMBRE = C.NO_MEMBRE

join dbo.VERSEMENT V
on V.NO_CONTRAT = C.NO_CONTRAT

join inserted i
ON i.NO_CONTRAT = V.NO_CONTRAT
go

This is a very helpfull example, but I am not sure of a couple of points eg.
V.MONTANT

where does the V come from? That is, at what point does SQL know what the
meaning of V is, as it hasnt been mentioned in the previous lines of code.

I presume that it represents the table dbo.MEMBRE (and that V.MONTANT is a
field MONTANT in table V )

Am I on the right track?

Many thanks for any help


Jul 20 '05 #6
"Ray Watson" <RW@IINET.NET.AU> wrote in message news:<40**********************@freenews.iinet.net. au>...

Assuming that NO_CONTRAT is the primary key for dbo.VERSEMENT, then this
looks like it may be what you want, although I'm not sure:

CREATE TRIGGER MONTANT_VERSEMENT ON dbo.VERSEMENT
FOR INSERT
AS

if @@rowcount = 0
return

UPDATE dbo.MEMBRE
SET SOLDE = SOLDE + V.MONTANT
FROM dbo.MEMBRE M
join CONTRAT C
on M.NO_MEMBRE = C.NO_MEMBRE

join dbo.VERSEMENT V
on V.NO_CONTRAT = C.NO_CONTRAT

join inserted i
ON i.NO_CONTRAT = V.NO_CONTRAT
go

This is a very helpfull example, but I am not sure of a couple of points eg.
V.MONTANT

where does the V come from? That is, at what point does SQL know what the
meaning of V is, as it hasnt been mentioned in the previous lines of code.

I presume that it represents the table dbo.MEMBRE (and that V.MONTANT is a
field MONTANT in table V )

Am I on the right track?

Many thanks for any help

V is an alias for dbo.VERSEMENT (ie join dbo.VERSEMENT V)
It is common convention for an alias to the first character of the
table name (as above)

MONTANT is therefore a field in the VERSEMENT table.

The update query may be written as follows:

UPDATE MEMBRE
SET SOLDE = SOLDE + VERSEMENT.MONTANT
FROM MEMBRE
join CONTRAT on MEMBRE.NO_MEMBRE = CONTRAT.NO_MEMBRE
join VERSEMENT on VERSEMENT.NO_CONTRAT = CONTRAT.NO_CONTRAT
join inserted ON inserted.NO_CONTRAT = VERSEMENT.NO_CONTRAT
Jul 20 '05 #7
Simon Hayes (sq*@hayes.ch) writes:
"Fernand St-Georges" <fe****************@videotron.ca> wrote in message
can someone tell how to write a Trigger; I am familiar with Sybase Sql
Anywhere trigger syntax.

...
The AFTER trigger syntax should be more or less the same as Sybase.


Simon, Fernand said Sybase SQL Anywhere, which is a different product
that the plain Sybase SQL Server with which Microsoft SQL Server share
heritage.

Sybase Anywhere was once known as Watcom SQL, before Sybase acquired
that company. I have never seen it, but since it has a different history,
it is not likely to share more syntax with SQL Server than any other
random engine.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8
Erland said

"it is not likely to share more syntax with SQL Server than any other
random engine"
in fact in Sybase Anywhere I had the choice to write triggers using
Watcom Sql which is more like natural language or Transac Sql

Anywhere even translated automaticly from one to another

but actually in Sql Server, the language is different and have to write
triggers that are not in a natural language

"CREATE TRIGGER MAJ_SOLDE ON [VERSEMENT]
FOR
UPDATE [MEMBRE]
as
SET [MEMBRE].SOLDE = [MEMBRE].SOLDE + [VERSEMENT] .MONTANT
FROM [MEMBRE]
join [CONTRAT] on [MEMBRE].NO_MEMBRE = [CONTRAT].NO_MEMBRE
join [VERSEMENT] on [VERSEMENT] .NO_CONTRAT = [CONTRAT].NO_CONTRAT
join inserted ON inserted.NO_CONTRAT = [VERSEMENT] .NO_CONTRAT E
end"

now the message I get Incorrect syntaxe near 'MEMBRE'

Fernand St-Georges
Quebec City
fe****************@videotron.ca

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #9
fernand st-georges (fe****************@videotron.ca) writes:
"it is not likely to share more syntax with SQL Server than any other
random engine"
in fact in Sybase Anywhere I had the choice to write triggers using
Watcom Sql which is more like natural language or Transac Sql

Anywhere even translated automaticly from one to another

but actually in Sql Server, the language is different and have to write
triggers that are not in a natural language

"CREATE TRIGGER MAJ_SOLDE ON [VERSEMENT]
FOR
UPDATE [MEMBRE]
as
SET [MEMBRE].SOLDE = [MEMBRE].SOLDE + [VERSEMENT] .MONTANT
FROM [MEMBRE]
join [CONTRAT] on [MEMBRE].NO_MEMBRE = [CONTRAT].NO_MEMBRE
join [VERSEMENT] on [VERSEMENT] .NO_CONTRAT = [CONTRAT].NO_CONTRAT
join inserted ON inserted.NO_CONTRAT = [VERSEMENT] .NO_CONTRAT E
end"

now the message I get Incorrect syntaxe near 'MEMBRE'


That syntax is not legal synrax in MS SQL Server, and was not legal
syntax in Sybase SQL Server last time I saw it, but I have only worked
with Sybase 4.x. Then again, looking at
http://manuals.sybase.com:80/onlineb...eric__BookView
I see no mention of the above syntax, so I would assume that it is
specific to Sybase Anywhere.

The correct syntax in SQL Server would be:

CREATE TRIGGER MAJ_SOLDE ON [VERSEMENT]
FOR
UPDATE
as
UPDATE MEMBRE
SET SOLDE = [MEMBRE].SOLDE + [VERSEMENT] .MONTANT
FROM [MEMBRE]
join [CONTRAT] on [MEMBRE].NO_MEMBRE = [CONTRAT].NO_MEMBRE
join [VERSEMENT] on [VERSEMENT] .NO_CONTRAT = [CONTRAT].NO_CONTRAT
join inserted ON inserted.NO_CONTRAT = [VERSEMENT] .NO_CONTRAT

That is, the trigger body must have a complete UPDATE statement, you
should not prefix the column on left-hand side of the SET clause, and
the END should not be there.

You should probably remove VERSEMENT from the FROM clause as well; I
cannot see that you really need it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Elvira Zeinalova | last post: by
2 posts views Thread by Brian Salentine | last post: by
4 posts views Thread by d0wsdkn02 | last post: by
9 posts views Thread by dotnetfellow | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.