Re: SPs or triggers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: SriSamp (ssampath_at_sct.co.in)
Date: 07/17/04 

Date: Sat, 17 Jul 2004 10:56:54 -0400

You would be better off if you are using stored procedures mainly because of
security. If you are implementing your application through triggers and
views, you need to give access to the users to query those tables and you
would be sending ad-hoc query statements from your application to SQL Server
and that's not a good idea. Stored procedures help you abstract your
underlying schema in a much better way and offer very good performance.
Also, the call syntax is highly simplified from your application as you
would just mention the SP name and its parameters. Finally, security is
greatly improved, since all one would need would be EXECUTE permissions over
the SP. No other permission is required. 

-- 
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"viketo" <viketo@yahoo.com> wrote in message
news:uWzsEw9aEHA.1764@TK2MSFTNGP10.phx.gbl...
> Hi all,
>
> I hesitate what to use SPs or triggers. I would like my queries to be as
> fast as possible. My data base is something like this:
>
> UsersTable
> ------------
> UserID(PK) | UserName | Password | Name | Addr | E-mail | UsrType | Role |
> LastLogOn
>
> EstatesTbale
> -----------
> EstateID(PK) City Phone Heating Furniture TypeEstate(FK) TypeOffr(FK)
> TypeConstr(FK)
>
> OfferTable
> ------------
> EstateID(FK) UserID(FK) Value ParamID(FK)
>
> Param Table
> --------------
> ParamID(PK) Param
>
> TypeEstateTable
> ----------------
> Unikey(PK) TypeEstate
>
> TypeOFFRTable
> -------------
> Unikey(PK) TypeOffr
>
> TypeConstr
> -------------
> Unikey(PK) TypeConstr
>
> PictureEstateTable
> ---------------------
> ID(PK) EstateID(FK) PictureFilePath
>
> DescriptionEstateTable
> -------------------------
> ID(PK) EstateID(FK) TextDescription
>
>
> I develop Web based application using ASP.NET/C#/MS SQL. I'd like to ask
you
> would you change something in the tables? And also, I would like what
would
> be better:
> 1. To import user data by registration whit SP or Trigger?
> 2. When a registrated user would like to place an Advertisement what would
> be better again stored procedure or trigger?
> For this inserting I wrote my first SP:
>
> CREATE PROCEDURE prSVOFFRDATA
> @CITY VARCHAR(20),
> @PHONE CHAR(1), -- Insert 'Y' or 'N'
> @FURN CHAR(1), -- Insert 'Y' or 'N'
> @HEATING CHAR(1), -- Insert 'Y' or 'N'
> @TYPEESTATE CHAR(3),
> @TYPEOFFR INT,
> @TYPECONSTR CHAR(1),
> @USERID INT,
> @ValuePrice BIGINT,
> @ValueFloor BIGINT,
> @ValueArea BIGINT,
> @ValueRooms BIGINT,
> @PICTURE VARCHAR(50),
> @DESCRIPTION VARCHAR(255)
>
> AS
> INSERT INTO
> BLEK.EstatesTable(City,Phone,Furn,Heating,TypeEstate,TypeOffr,TypeConstr)
> VALUES(@CITY,@PHONE,@FURN,@HEATING,@TYPEESTATE,@TYPEOFFR,@TYPECONSTR)
> DECLARE @IDENT INT
> SELECT @IDENT = SCOPE_IDENTITY()
> INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID)
> VALUES(@IDENT,@USERID,@ValuePrice,'price')
> INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID)
> VALUES(@IDENT,@USERID,@ValueFloor,'floor')
> INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID)
> VALUES(@IDENT,@USERID,@ValueArea,'area')
> INSERT INTO BLEK.OFFERTABLE(EstateID,UserID,Value,ParamID)
> VALUES(@IDENT,@USERID,@ValueRooms,'room')
>
> IF (@PICTURE IS NOT NULL)
> INSERT INTO BLEK.PICTUREESTATETABLE(EstateID,PICTUREFILEPATH)
> VALUES(@IDENT,@PICTURE)
> IF (@DESCRIPTION IS NOT NULL)
> INSERT INTO BLEK.DSCRIPTIONESTATETABLE(EstateID,TextDescription)
> VALUES(@IDENT,@DESCRIPTION)
>
> You can see that it is my first SP:) I dont know how to optimize it. I
don't
> have an idea would be better if I CREATE VIEW and use TRIGGER to populate
> data in the tables. I have to UPDATE this rows when a user wants to change
> the data in his/her advertisement.
> I have also a big SEARCH query between 4 tables. What would you mean? To
> CREATE VIEW and to use SELCT QUERY from this VIEW or to create Stored
> PRocedure.
> Could help me please, could you give me an advice?
>
>
>
> Thank you very much!
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.
>
>

Relevant Pages

  • Re: Trigger, alternative way to pass variable to trigger
    ... What we did was have our object that we used to call stored procedures add ... Then in the trigger or default constraints, I used a user defined function ... system function, though just calling it as master.. ... >>Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • "on exception" statement not firing within stored procedure when called from trigger
    ... I am experiencing a problem whereby ON EXCEPTION statements are ... not firing within stored procedures when the stored procedures ... are invoked from a trigger. ... create table tBar ) lock mode row; ...
    (comp.databases.informix)
  • tcl sqlite stored procedures and triggers
    ... i was just looking at stored procedures and triggers. ... is some of this validation done at the form table level on an sqlite db? ... i scammed my ideas for triggers and stored procs from sqlitemanager on ... here was the code i used to make a trigger and stored procedure. ...
    (comp.lang.tcl)
  • Re: Permission tracking
    ... There is no primary key, lots of colums does allow NULL's ... create a trigger on this table and start to manipuilate with DELETED ... >> I suggest you using STORED PROCEDURES for security reasons. ...
    (microsoft.public.sqlserver.security)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)