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

fernand.st-georges@videotron.ca

[quoted text, click to view]

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.

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 ***

[quoted text, click to view]

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

/***********************************************************
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

/***********************************************************

[quoted text, click to view]


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

[quoted text, click to view]
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

Simon Hayes (sql@hayes.ch) writes:
[quoted text, click to view]

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, sommar@algonet.se

Books Online for SQL Server SP3 at

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
fernand.st-georges@videotron.ca

*** Sent via Developersdex http://www.developersdex.com ***

fernand st-georges (fernand.st-georges@videotron.ca) writes:
[quoted text, click to view]

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/onlinebooks/group-as/asg1250e/refman/@Generic__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, sommar@algonet.se

Books Online for SQL Server SP3 at