StrataFrame Forum

Database Deployment Toolkit, view and trigger

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

By Olivier - 1/16/2013

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,
By Ivan George Borges - 1/16/2013

Hi Olivier.

Hope this post can help:

http://forum.strataframe.net/FindPost28364.aspx
By Michel Levy - 1/16/2013

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.
By Olivier - 1/16/2013

@ivan and @Michel :

Thanks You , it ll help me so much.

thanks guys

Olivier,
By Olivier - 1/16/2013

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
By Michel Levy - 1/16/2013

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)

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;


2nd one (priority 2)

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




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)

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;


le 2ème script (priority 2)

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
...
By Olivier - 1/16/2013

Thanks Michel,

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

thanks for your time and help.