473,692 Members | 2,078 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.CONTRA C_ID = CONTRACT.CONTRA C_ID
and CONTRAT.MEMBER_ ID = MEMBRE.MEMBER_I D

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.c a
Jul 20 '05 #1
9 8022
"Fernand St-Georges" <fe************ ****@videotron. ca> wrote in message news:<LZ******* *************@w agner.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.CONTRA C_ID = CONTRACT.CONTRA C_ID
and CONTRAT.MEMBER_ ID = MEMBRE.MEMBER_I D

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.c a


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_VERSEME NT 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_CO NTRAT = CONTRAT.NO_CONT RAT
and CONTRAT.NO_MEMB RE = MEMBRE.NO_MEMBR E

select VERSEMENT.MONTA NT = @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_VERSEME NT 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_CO NTRAT = CONTRAT.NO_CONT RAT
and CONTRAT.NO_MEMB RE = MEMBRE.NO_MEMBR E

select VERSEMENT.MONTA NT = @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_VERSEME NT 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_CONTRA T"
*************** *************** *************** **************/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_TYPE_CONTRAT
GO

/*************** *************** *************** **************
Contrainte de clé étrangère "FK_DUREE_CONTR AT"
*************** *************** *************** **************/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_DUREE_CONTRA T
GO

/*************** *************** *************** **************
Contrainte de clé étrangère "FK_APPROCHE_PR OMOT"
*************** *************** *************** **************/

ALTER TABLE CONTRAT
DROP CONSTRAINT FK_APPROCHE_PRO MOT
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_PAIEME NT"
*************** *************** *************** **************/

ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_TYPE_PAIEMEN T
GO

/*************** *************** *************** **************
Contrainte de clé étrangère "FK_MODE_PAIEME NT"
*************** *************** *************** **************/

ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_MODE_PAIEMEN T
GO

/*************** *************** *************** **************
Contrainte de clé primaire "PK_APPROCHE_PR OMOT"
*************** *************** *************** **************/

ALTER TABLE APPROCHE_PROMOT ION
DROP CONSTRAINT PK_APPROCHE_PRO MOT
GO

/*************** *************** *************** **************
Table "APPROCHE_PROMO TION"
*************** *************** *************** **************/

IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'APPROCHE_PROMO TION')
BEGIN
DROP TABLE APPROCHE_PROMOT ION
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_CONTR AT_P"
*************** *************** *************** **************/

ALTER TABLE DUREE_CONTRAT
DROP CONSTRAINT PK_DUREE_CONTRA T_P
GO

/*************** *************** *************** **************
Table "DUREE_CONT RAT"
*************** *************** *************** **************/

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_PAIEME NT_P"
*************** *************** *************** **************/

ALTER TABLE MODE_PAIEMENT
DROP CONSTRAINT PK_MODE_PAIEMEN T_P
GO

/*************** *************** *************** **************
Table "MODE_PAIEM ENT"
*************** *************** *************** **************/

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_MUNICIPALIT E"
*************** *************** *************** **************/

ALTER TABLE MUNICIPALITE
DROP CONSTRAINT PK_MUNICIPALITE
GO

/*************** *************** *************** **************
Table "MUNICIPALI TE"
*************** *************** *************** **************/

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_CONTRA T_PR"
*************** *************** *************** **************/

ALTER TABLE TYPE_CONTRAT
DROP CONSTRAINT PK_TYPE_CONTRAT _PR
GO

/*************** *************** *************** **************
Table "TYPE_CONTR AT"
*************** *************** *************** **************/

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_PAIEME NT_P"
*************** *************** *************** **************/

ALTER TABLE TYPE_PAIEMENT
DROP CONSTRAINT PK_TYPE_PAIEMEN T_P
GO

/*************** *************** *************** **************
Table "TYPE_PAIEM ENT"
*************** *************** *************** **************/

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_P RIM
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_PROMO TION"
*************** *************** *************** **************/

CREATE TABLE APPROCHE_PROMOT ION
(
APPROCHE_PROMOT ION varchar(50) NOT
NULL
)
GO

ALTER TABLE APPROCHE_PROMOT ION
ADD CONSTRAINT PK_APPROCHE_PRO MOT
PRIMARY KEY (APPROCHE_PROMO TION)
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_F INA datetime
NULL,
NUMERO_MEMBRE numeric(4) NOT
NULL,
ID_TYPE_CONTRAT numeric(4) NOT
NULL,
APPROCHE_PROMOT ION 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_CONT RAT"
*************** *************** *************** **************/

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

ALTER TABLE DUREE_CONTRAT
ADD CONSTRAINT PK_DUREE_CONTRA T_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_NAISSAN CE datetime
NULL,
TELEPHONE_TRAVA IL 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_PAIEM ENT"
*************** *************** *************** **************/

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

ALTER TABLE MODE_PAIEMENT
ADD CONSTRAINT PK_MODE_PAIEMEN T_P
PRIMARY KEY (MODE_PAIEMENT)
GO

/*************** *************** *************** **************
Table "MUNICIPALI TE"
*************** *************** *************** **************/

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

ALTER TABLE MUNICIPALITE
ADD CONSTRAINT PK_MUNICIPALITE
PRIMARY KEY (NO_MUNICIPALIT E)
GO

/*************** *************** *************** **************
Table "TYPE_CONTR AT"
*************** *************** *************** **************/

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_CONTRA T)
GO

/*************** *************** *************** **************
Table "TYPE_PAIEM ENT"
*************** *************** *************** **************/

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

ALTER TABLE TYPE_PAIEMENT
ADD CONSTRAINT PK_TYPE_PAIEMEN T_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_P RIM
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_CONTRA T)
REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRA T)
GO

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

ALTER TABLE CONTRAT
ADD CONSTRAINT FK_APPROCHE_PRO MOT
FOREIGN KEY (APPROCHE_PROMO TION)
REFERENCES APPROCHE_PROMOT ION (APPROCHE_PROMO TION)
GO

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

ALTER TABLE MEMBRE
ADD CONSTRAINT MUNI_FK
FOREIGN KEY (NO_MUNICIPALIT E)
REFERENCES MUNICIPALITE (NO_MUNICIPALIT E)
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_PAIEMEN T
FOREIGN KEY (TYPE_PAIEMENT)
REFERENCES TYPE_PAIEMENT (TYPE_PAIEMENT)
GO

ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_MODE_PAIEMEN T
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_VERSEME NT 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.A U> 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_VERSEME NT 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.MONTA NT
FROM MEMBRE
join CONTRAT on MEMBRE.NO_MEMBR E = CONTRAT.NO_MEMB RE
join VERSEMENT on VERSEMENT.NO_CO NTRAT = CONTRAT.NO_CONT RAT
join inserted ON inserted.NO_CON TRAT = VERSEMENT.NO_CO NTRAT
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_CON TRAT = [VERSEMENT] .NO_CONTRAT E
end"

now the message I get Incorrect syntaxe near 'MEMBRE'

Fernand St-Georges
Quebec City
fe************* ***@videotron.c a

*** 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_CON TRAT = [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_CON TRAT = [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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11793
by: Bob Ganger | last post by:
Hello, I am working on a project using SQL Server 2000 with a database containing about 10 related tables with a lot of columns containing text. The total current size of the database is about 2 Gig. When I delete data from the database, it takes a lot of system resources and monopolizes the database so that all other query requests are slow as mud! Ideally, I would like to be able to issue delete commands to the database on a...
2
10630
by: M Wells | last post by:
Hi All, I'm a relatively newbie to SQL Server 2000, having come from a MySQL background. I'm creating my first Trigger statement on a table, and I'd like to know how I go about performing an update on the row that was changed when the trigger was fired. To explain, I have 2 columns, one which contains a member number, the
2
11729
by: Elvira Zeinalova | last post by:
Hei, We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated machines). I am triing to connect them så that when one row is added to the table in the database in main server - then the same row is added to the same table in the second server database. I made the insert trigger on the table in the first server ( the second server is added as a linked server):...
2
1876
by: Brian Salentine | last post by:
Hi - We have two SQL 2000 Servers. We have the linked server setup and we can perform updates and inserts between the databases. But when we add a trigger and insert something into a table, the database hangs. There are NO processes blocking or being block in either database. This ONLY occurs when we have one OS as Windows 2000 Server and the other OS as Windows 2003 Server. This problem does not occur when both servers are Windows...
17
3584
by: Trevor Best | last post by:
I don't know if this has been reported before but it appears to be a bug with Access. If I create two tables both with an identity column then create an insert trigger on table1 that inserts a related record into table2, now create a form on table1 with a subform on table2. Insert records into the main form to your heart's content and everything's fine, each main record automatically gets a child record and so far the identity columns...
6
2283
by: Marc | last post by:
How could I directly trigger a very simple on localhost and a known port listening server from my internet browser client? Local host means the little server would be running on the client machine, where my browser resides. Browser would be IE, O.S. Windows 2000 or XP, and it's for an intranet application. The goal of the little server on the localhost client side would be to trigger a scanner, with the TWAIN library. Also this server is...
4
3628
by: d0wsdkn02 | last post by:
I have an ASP.NET application that performs ADO.NET commands that cause triggers to be run in SQL Server 2000. My question is how would I best access the user ID from my custom .NET Principal in the trigger (the trigger stores audit information and needs the user ID)? I'm using the same SQL Server user/pass on every connection to facilitate connection pooling, so it's not available that way.
2
24751
by: niradjoshi | last post by:
I made two SQL Server 2000 as linked server using same remote login These both server are running on different Domain Configuration Detail 1. Server A - MS SQL Server 2000 SP4, windows 2003 standard edition SP1 2. Server B - MS SQL Server 2000 SP3, windows 2003 standard edition I have Created same login in both the servers called test
9
2482
by: dotnetfellow | last post by:
The system has worked for two years. SQL Server 2000 runs on Windows 2000 Server "A". Another instance of SQL Server 2000 was moved from Windows 2000 Server "B" old to Windows 2003 Server "B" new, by restoring a backed up copy from old to new. The system has cross server updates, where Server "A" is updating records in very large tables on Server "B".
0
8600
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8535
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8954
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8794
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8796
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5817
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4555
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
1955
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.