StrataFrame Forum » StrataFrame Application Framework - V1 » Enhancement Requests » Database Deployment Toolkit, view and trigger

Database Deployment Toolkit, view and trigger

Olivier — Wed, 16 Jan 2013 12:09:00 GMT

Hello,

It's possible to add trigger instead of in Database Deployment toolkit ?

1- When we deploy the database on each deployment , the view lose their trigger (update, delete, insert)
2- it's more easy if DDT can deploy trigger , to have one way solution to deploy the data.

thanks
Olivier,

RE: Database Deployment Toolkit, view and trigger

Olivier — Wed, 16 Jan 2013 12:09:00 GMT

Thanks Michel,

i understand the way, and i hope trent read us, to add this functionnality in DDT .

thanks for your time and help.

RE: Database Deployment Toolkit, view and trigger

Michel Levy — Wed, 16 Jan 2013 11:56:12 GMT

Olivier,

the scripts in DDT are executed within through SMO, which is not a script language. So you must not include the GO term, which is not in T-SQL syntax but is a scripting language term.

You don't need to specify USE Imhothep, because it set by the DDT profile script setting "Execute on Database".

In your example, you should have 2 scripts:

1st one (priority 1)

[code]IF OBJECT_ID ('trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue','TR') IS NOT NULL
DROP TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue;
[/code]

2nd one (priority 2)

[code]CREATE TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue ON v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue
INSTEAD OF INSERT --INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
...
[/code]

[hr]

Olivier,

Les scripts de DDT sont exécutés par l'intermédiaire de SMO, qui n'est pas un language de scripts. Tu ne dois donc pas y mettre le mot GO, qui n'appartient pas à la syntaxe T-SQL mais qui est un terme de langage de script.

Tu n'as pas besoin non plus de spécifier USE Imhotep, puisque c'est défini dans la propriété "Execute On Database" du script de profil.

Dans ton cas, tu devrais avoir 2 scripts:

1er script (Priority 1)

[code]IF OBJECT_ID ('trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue','TR') IS NOT NULL
DROP TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue;
[/code]

le 2ème script (priority 2)

[code]CREATE TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue ON v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue
INSTEAD OF INSERT --INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
...
[/code]

RE: Database Deployment Toolkit, view and trigger

Olivier — Wed, 16 Jan 2013 09:34:28 GMT

Hello,

I set my procedure which work on sql management studio, fail in DDT

He says : an error occured on the script 'create trigger iof insert'

Syntax incorrect to 'GO'

Source : Net.SqlClient Data Provider.

Create trigger must to be first instruction of traitment query.

Start of my script

USE Imhotep
GO
IF OBJECT_ID ('trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue','TR') IS NOT NULL
    DROP TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue;
GO

CREATE TRIGGER trigger_insert_v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue ON v_utilisateur_utilisateuragence_agence_t_groupement_contact_telephone_email_adresse_communes_rue
INSTEAD OF INSERT --INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
    BEGIN TRANSACTION
        SET NOCOUNT ON

Have you an idea ?

thanks
Olivier

RE: Database Deployment Toolkit, view and trigger

Olivier — Wed, 16 Jan 2013 07:59:58 GMT

@ivan and @Michel :

Thanks You , it ll help me so much.

thanks guys

Olivier,

RE: Database Deployment Toolkit, view and trigger

Michel Levy — Wed, 16 Jan 2013 07:55:11 GMT

Hi Olivier,

Alas, DDT does not directly support Triggers Instead Of (maybe in the next version).

But there is a very simple workaround I use: in your DDT profile, add a New Profile Script, set it as Post-Deploy Script, set the database to be executed on.
Simply fill the T-SQL with the code you need (retrieve it from SSMS), don't forget the IF EXISTS clause.

If you need more than one script, and if one depends on another, the Priority setting defines the order the scripts will be executed.

RE: Database Deployment Toolkit, view and trigger

Ivan George Borges — Wed, 16 Jan 2013 07:44:17 GMT

Hi Olivier.

Hope this post can help:

http://forum.strataframe.net/FindPost28364.aspx