This page needs a newer browser to run its tools. You can still read everything here.

PL/SQL & T-SQL (Oracle and SQL Server) Module 6

Dynamic SQL and triggers in T-SQL interview questions

Interview questions on dynamic SQL and triggers in T-SQL: warm-ups, then what each harder question tests and how a good answer starts.

8 questions: 3 warm-ups and 5 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

How do EXEC(@sql) and sp_executesql differ?

Answered in the lesson Dynamic SQL with sp_executesql, safely
Fresher to mid

What do the inserted and deleted tables hold?

Answered in the lesson T-SQL triggers: inserted, deleted and pitfalls
Fresher to mid

Does an AFTER UPDATE trigger fire when the UPDATE matches no rows?

Answered in the lesson T-SQL triggers: inserted, deleted and pitfalls

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A security test of your stock search reports that the name filter returns every item for a crafted input. The procedure concatenates optional filters for part of the name, the SKU and a maximum price, and sorts by a column and direction the caller names. Explain the weakness, rewrite it safely and describe a test that proves the fix.
What it tests

Recognising pasted input, passing every filter value, a LIKE pattern included, as a typed parameter, handling the caller's sort column and direction without pasting text, and proving the fix with a test that fails on the old code.

First step

The filters paste the caller's text into the statement, so a crafted name ends the string literal and adds a condition of its own. Keep the optional filters dynamic if that helps the plans, but pass every value as a typed parameter of sp_executesql, the LIKE pattern too.
Follow-ups
- How do you keep optional filters without a plan for every combination of values?
- What do the sort column and the direction need that the filters do not?
- What does your test send, and what must it see?
From the lesson Dynamic SQL with sp_executesql, safely
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A report procedure used to read dbo.orders directly. After a change that builds its query as dynamic SQL, so users can choose columns, every report user gets error 229, SELECT permission denied, although they still have EXECUTE on the procedure. Explain why, and fix it without granting the tables to the users.
What it tests

Knowing that ownership chaining covers the module's own statements but not dynamic SQL, and choosing a least-privilege fix such as EXECUTE AS a dedicated user or module signing.

First step

Before the change the procedure and dbo.orders had the same owner, so the ownership chain spared the users a permission check on the table. Dynamic SQL is checked with the permissions of the user who runs it, and they have none on dbo.orders.
Follow-ups
- Why not grant SELECT on the tables to the report role?
- What does EXECUTE AS change, and what does it cost?
- How would you check that the fix gives no more than intended?
From the lesson Dynamic SQL with sp_executesql, safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Since an audit trigger was added to dbo.orders, the checkout sometimes links a payment to the wrong order or to an order that does not exist. The insert procedure reads SELECT @@IDENTITY right after its INSERT INTO dbo.orders. Explain the bug, and fix it for one order and for a basket of many.
What it tests

Knowing the scope of @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT, and returning new keys with OUTPUT INTO when the table has triggers.

First step

Compare what each identity function reports right after the INSERT on the lab schema, with the audit trigger in place, and see which scope each one looks at.
Follow-ups
- Why did the code work before the trigger existed?
- Why not IDENT_CURRENT('dbo.orders')?
- How do you return the keys of a 30-line basket?
From the lesson T-SQL triggers: inserted, deleted and pitfalls
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly job loads 10,000 price rows into dbo.stock with one INSERT … SELECT. A trigger refuses prices above a ceiling with THROW, and every few nights one bad row makes the whole load fail. The business wants the good rows loaded and the bad ones reported. Explain why the load fails as a whole, and redesign it.
What it tests

Knowing what a trigger's error does to its statement and transaction, and moving validation before the load so that good rows go in and bad rows are reported.

First step

Remember that the trigger runs once for the whole INSERT and is part of its transaction. Then decide where the ceiling check can run on the staged set, before any row reaches dbo.stock.
Follow-ups
- Where do the checks go instead?
- Should the trigger stay?
- How are the bad rows reported?
From the lesson T-SQL triggers: inserted, deleted and pitfalls
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

The finance team wants every past price of every item, with when it was valid, for disputes going back two years. A developer proposes an AFTER UPDATE trigger that copies old rows into dbo.price_history. Compare that design with a system-versioned temporal table, and choose one.
What it tests

Weighing a trigger-based audit against a temporal table: completeness, the paths each can miss, the queries the finance team needs, and the cost of the history.

First step

Write down what each design records for an UPDATE, a DELETE, a MERGE and a TRUNCATE, and how each answers the question "what was the price of this item on a given day".
Follow-ups
- How do you answer "what was the price on a given day" with each design?
- How do you keep the history from growing forever?
- When would you still choose the trigger?
From the lesson T-SQL triggers: inserted, deleted and pitfalls
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track

Warm-up questions

How do EXEC(@sql) and sp_executesql differ?

What do the inserted and deleted tables hold?

Does an AFTER UPDATE trigger fire when the UPDATE matches no rows?

Questions with model answers

Since an audit trigger was added to dbo.orders, the checkout sometimes links a payment to the wrong order or to an order that does not exist. The insert procedure reads SELECT @@IDENTITY right after its INSERT INTO dbo.orders. Explain the bug, and fix it for one order and for a basket of many.

The finance team wants every past price of every item, with when it was valid, for disputes going back two years. A developer proposes an AFTER UPDATE trigger that copies old rows into dbo.price_history. Compare that design with a system-versioned temporal table, and choose one.